VBA add-in When is ActiveWorkBook activated?
Context: Excel 2013 VBA. When using an add-in, the term "ActiveWorkBook" should refer to the document being edited, and "ThisWorkBook" refers to the add-in in the background. Consider the code
In the ThisWorkBook module, add-ins
Private WithEvents App As Application Private Sub Workbook_Open() Set App = Application End Sub Private Sub App_WorkBookOpen(ByVal Wb As Workbook) MsgBox Wb.Name & " " & Wb.Worksheets(1).Cells(1, 1) If Wb.Worksheets(1).Cells(1, 1) = "AAA" Then MsgBox "Cell OK", MsgBox ActiveWork.Name End If End Sub
The add-in is enabled and Excel is running. So far, so good. Now if I open the file "Book1" which contains "AAA" in cell (1,1) of Sheet1, I get:
"Book1.xlsm AAA" (in the message box as expected) followed by "Box OK" as expected.
But then the error "Required object" refers to the line MsgBox "ActiveWorkBook.Name" So, at that moment Book1 is not yet an ActiveWorkBook. When will it be like this? Or how to do it? (Something like "Wb.Activate" before MsgBox doesn't help)
This problem manifests itself in a much more complex situation in the real world, which nevertheless seems to be related to security issues. I am trying to understand the behavior with a simple example
source to share
You are not handling the case where the workbook is not active.
is called before the opening workbook is opened, so it's
when this code runs - anytime Excel actually starts up.
The simple solution is to use link
will not be installed until this event completes. And if it is installed, then this is not the workbook that you think so: this is the workbook that was active at startup
See for yourself (in addition to the
Private WithEvents app As Application Private Sub app_WorkbookActivate(ByVal Wb As Workbook) MsgBox "activated" End Sub Private Sub app_WorkbookOpen(ByVal Wb As Workbook) MsgBox "opened" End Sub Private Sub Workbook_Open() Set app = Excel.Application End Sub
When the add-in starts, you will see an "open" message box (while the background still shows an empty workspace without any workbook) - and after that an activated message box appears as soon as Excel actually has a worksheet on display.
source to share