Enabling/Disabling SubMenuItems on the Menu Bar

[Guest]

I have created a custom menu item for the application being created in Excel
via VB code. However, I'm wanting to disable one of the submenu items until
a certain worksheet is selected or perhaps just make it invisible until this
said worksheet is selected. I'm rather new to VB coding but not to coding by
any stretch. Can anyone provide a short example of how do this from another
code module than the one which created the menu and submenu items? I'm sure
I'm not doing the explicit notation correctly and I should be able to just
call the right submenu item by name, id, or perhaps tag without looping
through them all, right?

Thanks,

Bryan

 

[Ron de Bruin]

Hi Bryan

See
http://www.rondebruin.nl/menuid.htm

For the workbook run the code in this two events in the ThisWorkbook module

Read more about events on Chip Pearson's site.
http://www.cpearson.com/excel/events.htm


Private Sub Workbook_Activate()
'your code to change Enabled to False
'Or the name of your macro
End Sub

Private Sub Workbook_Deactivate()
'your code to change Enabled to True
'Or the name of your macro
End Sub

 

[acampbell012]

In the workbook module, this fires routine that creates the menu:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Log_Menu
End Sub

'fires delete_menu routine that removes the menu item if current
workbook/window is deactivated.

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Delete_Menu
End Sub

Private Sub Workbook_Deactivate()
Delete_Menu
End Sub