Menu in the Active Workbook

This demonstrates how to add a menu that is visible only when the source workbook is active.

Here is a link to the demonstration file which contains all the code described on this page: MenuActiveWbDemo

The code is in two modules:

ThisWorkbook - which controls when the menu is activated and deactivated.
BygMacs - all the other code.

ThisWorkbook Code

This consists of three stub routines that call other routines (which do the hard work).

These three make the menu activate and deactivate as uses switch between this and other workbooks.

'' ***************************************************************************
'' Purpose : Prepare and display the menu for this workbook
'' Written : 12-Jan-1999 by Andy Wiggins, Byg Software Limited
''
Sub Workbook_Open()
''Call the 'sub' so code is only modified in one place
	Workbook_Activate
End Sub

'' ***************************************************************************
'' Purpose : Prepare and display the menu for this workbook
'' Written : 12-Jan-1999 by Andy Wiggins, Byg Software Limited
''
Sub Workbook_Activate()
	xWorkbook_Activate
End Sub

'' ***************************************************************************
'' Purpose : Remove the menu associated with this workbook
'' Written : 12-Jan-1999 by Andy Wiggins, Byg Software Limited
''
Sub Workbook_Deactivate()
	xWorkbook_Deactivate
End Sub

BygMacs Code

There are several commented sections of code that you can use as alternatives - they concern the placing of the menu.

The code creates four menu items, one of which is not enabled and another which is removed before it even gets displayed. Why do that? Here it's done to illustrate the technique.

Option Explicit

Const cWmb = "Worksheet Menu Bar"
Const cMm = "&MenuDemo"

'' ***************************************************************************
'' Purpose  : Prepare and display the menu for this workbook
'' Written  : 12-Jan-1999 by Andy Wiggins, Byg Software Limited
''
Sub xWorkbook_Activate()
Dim x$
    
    ''Set an error trap
    On Error Resume Next
    
    ''If this item exists on the worksheet menu, then delete it
    ''This ensures that any existing version on the menu bar is deleted
    CommandBars(cWmb).Controls(cMm).Delete
    
    ''Create the menu
    CommandBars(cWmb).Controls.Add(Type:=msoControlPopup).Caption = cMm
    
    ''OR, if you want it in a particular place
    ''CommandBars(cWmb).Controls.Add(Type:=msoControlPopup, Before:=3).Caption = cMm
    
    ''OR, the following line can be used if the menu is require immediately before, e.g., "Help" (Workaround from MSDN)
    ''MenuBars(xlWorksheet).Menus.Add Caption:=cMm, Before:="Help"

    ''Use the menu to create the menu item(s)
    With CommandBars(cWmb).Controls(cMm)
        
        ''Add a separator bar before the menu name
        .BeginGroup = True
        
        x = "Menu Item &1"
        .Controls.Add(Type:=msoControlButton).Caption = x
        .Controls(x).OnAction = "DummyMessage"
        
        x = "Menu Item &2"
        .Controls.Add(Type:=msoControlButton).Caption = x
        With .Controls(x)
            .OnAction = "DummyMessage"
            .State = msoButtonDown
            .Enabled = False
            ''Add a separator bar before the menu item
            .BeginGroup = True
        End With

        x = "Menu Item &3"
        .Controls.Add(Type:=msoControlButton).Caption = x
        .Controls(x).OnAction = "DummyMessage"

        ''This deletes the above addition, so it will never appear on the menu
        .Controls(x).Delete

        x = "Menu Item &4"
        .Controls.Add(Type:=msoControlButton).Caption = x
        .Controls(x).OnAction = "DummyMessage"
        .Controls(x).BeginGroup = True

    End With

End Sub

'' ***************************************************************************
'' Purpose  : Remove the menu associated with this workbook
'' Written  : 12-Jan-1999 by Andy Wiggins, Byg Software Limited
''
Sub xWorkbook_Deactivate()
    ''Set an error trap
    On Error Resume Next

    ''When leaving, delete the menu associated with this workbook
    CommandBars(cWmb).Controls(cMm).Delete

End Sub

'' ***************************************************************************
'' Purpose  : Dummy message - demo only
'' Written  : 13-Apr-2003 by Andy Wiggins, Byg Software Limited
''
Sub DummyMessage()
    MsgBox "Menu item selected", vbInformation, "www.BygSoftware.com"
End Sub