CODEWITHSUNDEEP
excelvba
Shashiraju
Shashiraju

Reputation: 149

Workbook_Open event in Excel Add ins works only for first file opening

Workbook_Open event in Excel Add-ins works only for the first file but If I open next files keeping the first file is open it is not functioning, Can anyone please help me to resolve this issue?

I have used this below code in Thisworkbook module to create Add-in and selected Add-In name in options also

Code:

Sub Workbook_Open()
    MsgBox "Welcome..!!"
End Sub

Any help is appreciated..!! Thanks

Upvotes: 2

Views: 1194

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

It doesn't work for the first file opening.

ThisWorkbook represents the document that's hosting the VBA code - in this case, the add-in "workbook": this message box will pop when the add-in is opened, when Excel starts up.

If you want to run code whenever any workbook is opened, you need to handle events at the Application level.

Declare a WithEvents variable in your ThisWorkbook module:

Option Explicit
Private WithEvents AppEvents As Excel.Application

Assign that object reference on open:

Private Sub Workbook_Open()
    Set AppEvents = Me.Application
End Sub

Now select AppEvents from the left-side dropdown at the top of the code pane, and pick the WorkbookOpen event on the right-side dropdown - that will create a handler that looks like this:

Private Sub AppEvents_WorkbookOpen(Wb As Workbook)

End Sub

If you put MsgBox Wb.FullName here, you should get a message box with the newly opened workbook's path/filename every time a workbook is opened.

Upvotes: 3

Related Questions