[Solved] VBA: Menus

  • I have just made a custom menu with the name 'Singles'. I wish to remove the existing menu and replace it with my own on entry into the program and the reverse on leaving. How do I code this?

    Help appreciated.


  • This is some code that I have run before, I have since tweaked it, but this comes from the Walkenbach book "Excel 2002 Power Programming with VBA" and it gave me a good starting point to learn about the menu object.

    Public Sub MakeMenuBar()
    Dim NewMenuBar As CommandBar 'New Menu Bar
    Dim NewMenu As CommandBarControl 'New Menu
    Dim NewItem As CommandBarControl 'New menu items
    'delete the menu bar if it exists
    Call DeleteMenuBar
    'add the new menu bar
    Set NewMenuBar = CommandBars.Add(MenuBar:=True)
    'name the new menu and make it visible
    With NewMenuBar
    .Name = "MyMenuBar"
    .Visible = True
    End With

    CommandBars("Worksheet Menu Bar").FindControl(ID:=30002).Copy Bar:=CommandBars("MyMenuBar")
    'set the new menu drop dowon
    Set NewMenu = NewMenuBar.Controls.Add(Type:=msoControlPopup)
    'set the caption of the new menu
    NewMenu.Caption = "Report Main Menu"
    'add sub menu items
    Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton)
    With NewItem
    .Caption = "Show Main Menu"
    .OnAction = "ShowfrmCC" 'name of procedure to run
    End With

    End Sub

    Public Sub DeleteMenuBar()
    On Error Resume Next
    On Error GoTo 0
    End Sub

    I call the MakeMenuBar in the Workbook_Open event and DeleteMenuBar in Workbook_Close event.

    NOTE: This menu remains if a user has more than one workbook open you would need to adjust it in the WindowActivate() and WindowDeactivate() if you want to hide it when a user switches from one window to another.

    I am sure there are others that can give a better solution though.



  • Just as a point might be worth looking into the advantages and disadvantages / possibilities of an XLA for this an make the code execute on active sheet.


    BTW you can edit XLA just the same and will mean are available on open excel not a worksheet and delete on removal of add in

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!