0

I have a workbook (we'll call it "AAA") that is based on a template and refers to code modules in a second workbook. The second workbook (we'll call it "CodeStorage") is a repository for code modules so that any saved versions of the template will be able to access modified/updates code without a change to the saved workbook.

My problem arises when I have multiple windows open in "AAA" and try to get the activesheet when a module is running in "CodeStorage". In code, I create an object ("oWorkbook") that is a reference to the workbook "AAA" When "AAA" has focus, oWorkbook.Activesheet returns the sheet for the active window. When "CodeStorage" is running a module and thus has focus, oWorkbook.Activesheet returns the sheet that is selected in window #1 regardless of what window (#2, 3, etc) was active when the code module in "CodeStorage" was called.

Has anyone run into this and have you found a work around?

pnuts
  • 58,317
  • 11
  • 87
  • 139
T Cottengim
  • 61
  • 1
  • 12

2 Answers2

3

ActiveSheet is confusing the way you are using it.

You need to explicitly activate a sheet for it to be considered the ActiveSheet. Running code in another workbook does not activate it.

Selecting cells in a worksheet will activate it. Or specifically calling Activate.

You could do something like:

oWorkbook.Activate
oWorkbook.Activesheet

Alternatively, and preferably, you could do something like the following:

oWorkbook.Worksheets("Sheet1")
oWorkbook.Worksheets(1)

Both these are better. If your user selects a different workbook during runtime execution or you select something in a different sheet, ActiveSheet will return something different.

It's better to fully qualify you workbook paths when using multiple workbooks. This will save you a ton of headache in the future for dealing with "what is activated?" or "what is selected?" This answer is worth reading, too.

Community
  • 1
  • 1
enderland
  • 13,825
  • 17
  • 98
  • 152
0

To rephrase my question, I was looking for a way to reference the last active worksheet in a workbook that had multiple windows open. If window 1 was selected, I wanted the sheet from window 1, same for window 2, 3, or ???. I also needed the reference when a code module in a different workbook was running and the code module had a variable that was an object reference to the calling workbook.

The solution is Workbook.Windows.Item(1).ActiveSheetView.Sheet. When called from the running code module, even in a different workbook, it returns the same as workbook.activesheet when that is called from the workbook itself. My tests show that Workbook.Windows.Item(1) is the last active window when a workbook loses focus.

Thank you enderland. You got me pointed in the right direction.

T Cottengim
  • 61
  • 1
  • 12