Menu and sub-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: MenuActiveWbSubsDemo

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.

The code (highlighted in blue) adds a sub-menu with three items.

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$

    ''If this item exists on the worksheet menu, then remove it
    ''This ensures that any existing version on the menu bar is deleted
    DeleteCommandBarControl cMm
    
    ''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

        ''## Adding a sub menu with three items
        .Controls.Add(Type:=msoControlPopup).Caption = "Sub&Menu"

        With .Controls("SubMenu")

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

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

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

        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  : When leaving, delete the menu associated with this workbook
'' Written  : 12-Jan-1999 by Andy Wiggins, Byg Software Limited
''
Sub xWorkbook_Deactivate()
    DeleteCommandBarControl cMm
End Sub

'' ***************************************************************************
'' Purpose  : Delete a named command bar control
''          : Cycle through all existing names - if our one exists, delete it
'' Written  : 28-Mar-2001 by Andy Wiggins, Byg Software Limited
''
Sub DeleteCommandBarControl(menuItem)
Dim mb
    For Each mb In CommandBars(cWmb).Controls
        If mb.Caption = menuItem Then
            mb.Delete
        End If
    Next
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