Disable Menu (CommandBar) if not workbook is opened

[LuisE]

I have a menu from an Add-In, when all workbooks are closed and all the other
menus are disabled mine remains enabled.
How can I disabled it so if someone clicks it no error message is generated?
Just as the built-in ones.

Thanks in advance

 

[Ron de Bruin]

Do you create the menu with VBA code ?

You can create the menu in the Open even and close it in the beforeclose event
See this example

Debra Dalgleish's (Toolbar example from Dave Peterson)
http://www.contextures.com/xlToolbar02.html

 

[LuisE]

Thanks Ron, merry Christmas by the way.

Yes I did.
My concern is that the Add-In will remain installed even if not workbook in
opened so I'd imagine that it is technically not closed, is it?

 

[Ron de Bruin]

A add-in will close when you close Excel.

Can you explain more what you want ?
Do you have problems when people use your menu with no workbook open ?

 

[Ron de Bruin]

You can add this to your macro if you not want to run the code if there is no
workbook open.
If there are a lot of macros you can also build a function to do the test so you only
have to add one code line to each macro.

On Error GoTo QuitOpen
ActiveWorkbook.Activate
'Your macro code
Exit Sub
QuitOpen:
MsgBox "There is no file open", , "YourProgName"
Exit Sub

 

[LuisE]

It works fine, thank you ron

You can add this to your macro if you not want to run the code if there is no
workbook open.
If there are a lot of macros you can also build a function to do the test so you only
have to add one code line to each macro.

On Error GoTo QuitOpen
ActiveWorkbook.Activate
'Your macro code
Exit Sub
QuitOpen:
MsgBox "There is no file open", , "YourProgName"
Exit Sub