Workbook_Open not executing from Personal workbook

[macfuller]

Excel 2013 64-bit

I have procedures to add right-click menu options that I want to call whenever a workbook is open. The code is in the ThisWorkbook portion of personal.xlsb.

Private Sub Workbook_Open()
AddShortCuts
AddNewVBEControls
End Sub

However, the code is not executing when i open either existing or new spreadsheets. Enable all macros and trust the VBA model are set in Options.

For security reasons I can't send .xlam files to various users so I can't embed macros in the spreadsheets themselves. I don't know if my IT department set an obscure policy flag since this used to work reliably.

 

[shg]

Option Explicit

Dim WithEvents app As Application

Private Sub Workbook_Open()
  Set app = Excel.Application
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
  AddShortCuts
AddNewVBEControls
End Sub

 

[macfuller]

Thank you. I'd seen that in another thread and tried it with failure. It turns out I needed to shut down Excel and restart for the new code to work.

I also have a close event to clear the controls from the menus. I'll assume that also has to be fired by the App event. Should I also set App = nothing in the close event?

 

[shg]

It turns out I needed to shut down Excel and restart for the new code to work.

That's one way, or you could have just executed the Workbook_Open event.

I also have a close event to clear the controls from the menus.

In app_WorkbookClose

I also set App = nothing in the close event?

Not if you want the code to run the next time you open a workbook.

 

[macfuller]

If I close all my workbooks and open a new one, the App_WorkbookOpen does not fire. If however I close all workbooks and open an existing file the code does fire. Since the added menu options are removed when I close all workbooks is there a way to fire the event for a new workbook so my menu options come back?

 

[shg]

If I close all my workbooks and open a new one, the App_WorkbookOpen does not fire.

Private Sub app_NewWorkbook(ByVal Wb As Workbook)
  ' ...
End Sub

 

[macfuller]

When I start Excel both NewWorkbook and Workbook_Open are firing giving me duplicate menu items. Not a big deal, but what sort of test can I run to block one of them? Interesting Workbook_Open is triggered for a new sheet when opening Excel but not when opening a new sheet when Excel is already active!

 

[shg]

What's the order of events?

 

[macfuller]

Workbook_Open, then New Workbook

 

[shg]

Create a static variable to contain a workbook name.

In the Open event, set the variable to the workbook name. In the other event, check to see if the name is the same as the one you already processed.