VBA: Adding a menubar

  • Help...I am trying to create a menu + submenu items which appear on loading and delete on closing...the code I have wriiten is below but the menu does not appear...any suggestions on what needs changing?


    Private Const calculations_menu = "Data Dump"
    Private Const calculations_submenu_calculation = "Calculation"
    Private Const calculations_submenu_clearformats = "Clearformats"
    Private Const calculations_submenu_cleareverything = "Cleareverything"


    Sub buildcalculationsmenu(destroy As Boolean)
    Dim cbrtools As CommandBar
    Dim cbrnewmenu As CommandBarPopup
    Dim cbbnewmenuitem As CommandBarButton
    Dim intindex As Integer
    Dim intbar As Integer
    On Error Resume Next
    If destroy Then
    Set cbrtool = Application.CommandBars(1)
    cbrtools.Controls(calculations_menu).Delete
    Else
    Set cbrtools = Application.CommandBars(1)
    If cbrtools Is Nothing Then
    MsgBox "Cannot add a menu.", vbCritical, "error"
    End Sub
    Else
    cbrtools.Controls(calculations_menu).Delete
    Set cbrnewmenu = cbrtools.Controls.Add(Type:=msoControlPopup, Before:=cbrtools.Controls.Count + 1)
    cbrnewmenu.Caption = calculations_menu
    Set cbbnewmenuitem = cbrnewmenu.Controls.Add(Type:=msoControlButton, Before:=1)
    With cbbnewmenuitem
    .Caption = calculations_submenu_calculation
    .OnAction = "Calculation"
    .BeginGroup = True
    End With
    Set cbbnewmenuitem = cbrnewmenu.Controls.Add(Type:=msoControlButton, Before:=2)
    With cbbnewmenuitem
    .Caption = calculations_submenu_cleareverything
    .OnAction = "Cleareverything"
    .BeginGroup = True
    End With
    Set cbbnewmenuitem = cbrnewmenu.Controls.Add(Type:=msoControlButton, Before:=3)
    With cbbnewmenuitem
    .Caption = calculations_submenu_clearformats
    .OnAction = "Clearformats"
    .BeginGroup = True
    End With
    End If
    End If
    Set cbrtools = Nothing
    Set cbbnewmenuitem = Nothing
    End Sub

  • Hi,


    The method advocated in the link provided by Iridium is very useful if you have lots of menu items to deal with. However, if you have only a small number, or just want to do it the long way ;;) , then try the following minor adjustments to your code:

    Use the two mini subs to Add and Remove the menu using the main routine.


    HTH


    PS. Where are the integer variables (intindex and intbar) used?

Participate now!

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