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. Workbook_Open
is called before the opening workbook is opened, so it's Application.ActiveWorkbook
very possible Nothing
when this code runs - anytime Excel actually starts up.
The simple solution is to use link wb
in Workbook_Open
- ActiveWorkbook
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 wb
.
See for yourself (in addition to the ThisWorkbook
code-behind):
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