1

I have a workbook with a userform that contains a listbox that is used to populate data on a sheet.

If I have multiple workbooks open and I click from one workbook directly to the listbox on the userform in the other workbook, the ListBox_Change event fires before Activeworkbook changes to reflect the workbook that contains the userform. So when the code reaches Set EqDataSht = ActiveWorkbook.Worksheets("Equipment-Data") I get a subscript out of range error because the workbook I'm coming from doesn't contain a sheet named "Equipment-Data".

What is the best way to set the ActiveWorkbook to the parent of the userform? Thoughts I've had are setting a public variable wb = ActiveWorkbook on workbook_open or just trapping Err.Number=9 and telling the user to click on the sheet before clicking the userform. I'm sure there is something simple I am completely overlooking (VBA amateur).

Thoughts?

MattD
  • 150
  • 11

1 Answers1

3

Instead of activeworkbook use thisworkbook which returns the workbook in which the code resides.

To make it active thisworkbook.activate should work

gtwebb
  • 2,981
  • 3
  • 13
  • 22
  • Note: this only works if the UserForm operates against `ThisWorkbook`. If it needs to operate against other workbook(s) being *active* at runtime (not enough detail in OP to say definitively), this may not work, for the same reason. – David Zemens Apr 19 '17 at 20:25
  • Thanks! Couldn't be much simpler! To answer @DavidZemens, the workbook with the userform is entirely self contained, i.e. does not act on any other workbook. – MattD Apr 19 '17 at 20:27
  • @MattD then that approach will work in *this* instance, but it will not work in more complicated scenarios you might encounter in the future :) – David Zemens Apr 19 '17 at 20:30
  • 2
    @MattD you shouldn't need to `Activate` it at all, you can simply refer to `ThisWorkbook.Worksheets...` instead of `ActiveWorkbook.Worksheets...` and that way it will work without needing to explicitly make any sheet active. There are other reasons why you may want to activate a sheet, from a UI or design perspective, but it's definitely not necessary to do so. – David Zemens Apr 19 '17 at 20:31
  • @DavidZemens Thanks! I did a a find/replace ActiveWorkbook with ThisWorkbook in my module since it will work for this sheet. I'll have to do some studying about when it is best to use each. – MattD Apr 19 '17 at 20:33
  • 2
    It's never best to rely on `ActiveWorkbook`, `Select`, `Selection`, etc.. In some cases it's *easier*, but it's never *better*. [Detailed explanation and how to avoid those things, here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – David Zemens Apr 19 '17 at 20:35