Command bar help

[BigDelGooner]

Hi all

I am trying to create a command bar whereby a dropdown list consists of 3 groups which each have a sub menu.

I know the basic commandbar.controls.add function but dont know how to create the sub menus for each...

Anyone able to help?

 

[Richard Schollar]

Hi

Here's what I use in the Hmtlmaker to generate a top level menu bar item and then provide an option within this which provides a further menu of 5 items:

Dim cmdbr As CommandBar, cbc As CommandBarControl, cbcNew As CommandBarControl
Dim cbcOpt As CommandBarControl
 
'capture menu bar:
Set cmdbr = Application.CommandBars("Worksheet Menu Bar")
 
'Add new addition to menu bar (MrExcelHtml):
Set cbc = cmdbr.Controls.Add(Type:=msoControlPopup, temporary:=True)
 
'provide this new menu option with properties:
With cbc
    .Caption = "&MrExcelHtml"
    .Visible = True
    .Tag = "MrExcelHtml"
    .TooltipText = "Ouput range as html"
    .Move before:=cmdbr.Controls.Count - 1
End With
 
'add new option within MrExcelhtml:
    Set cbcNew = CreateControl(cbc, "G&enerate Html (specify option)", "Produce html without modifying default option", msoControlPopup)
 
'add further menu items which popup once above item hovered over:
    Set cbcOpt = CreateControl(cbcNew, "&All Formulas", "Output all formulas", msoControlButton, AllFormulas, "'make_grid AllFormulas'")
    Set cbcOpt = CreateControl(cbcNew, "&First Cell Only", "Output only the first formula", msoControlButton, FirstCell, "'make_grid FirstCell'")
    Set cbcOpt = CreateControl(cbcNew, "F&irst Cell In Each Column", "Output only the first formula in each column", msoControlButton, FirstCellInColumn, "'make_grid FirstCellInColumn'")
    Set cbcOpt = CreateControl(cbcNew, "&No Formulas", "Don't transcribe any formulas", msoControlButton, NoFormulas, "'make_grid NoFormulas'")
    Set cbcOpt = CreateControl(cbcNew, "&User Defined Selection", "User specifies formulas to output", msoControlButton, UserDefined, "'make_grid UserDefined'")
 
 
 
 
'#######Separarte function:########
 
Function CreateControl(container As Variant, strCap As String, strTip As String, lngType As MsoControlType, Optional tagLine, Optional Macro) As CommandBarControl
    Dim ctrl
    Set ctrl = container.Controls.Add(lngType)
    With ctrl
        .Caption = strCap
        .TooltipText = strTip
        If Not IsMissing(tagLine) Then .Tag = tagLine
        If Not IsMissing(Macro) Then .OnAction = Macro
    End With
    Set CreateControl = ctrl
End Function

I've commented where I thought necessary.

Let me know if you want the html maker file and you can check it out for yourself.

 

[BigDelGooner]

Thank you for this Richard. But where I am getting stuck is that this creates a single menu with 5 sub menus, however I needs several menus each with multiple sub menus...

 

[Richard Schollar]

Sorry, I don't understand - why can't you tailor the above to your situation?

 

[BigDelGooner]

The problem i get with trying to tailor your solution is that it just adds another sub menu to the last sub menu created, in other words it does not go back to the 'top level' when adding the second menu (which then leads onto the sub menus).

Here is the structure I would like to create...

Dropdown Menu
- Main menu 1
- Sub menu 1
- Sub menu 2
- Sub menu 3
- Main menu 2
- Sub menu 4
- Sub menu 5
- Sub menu 6
- Main menu 3
- Sub menu 7
- Sub menu 8
- Sub menu 9

Hopefully this is a bit clearer. Thanks for your help.

 

[Richard Schollar]

Try running the following - it will hopefully do what you want (show you how to do this - obviously it won't have your functionality attached to each button!):

Sub BigDelGooner()
Dim cmdbr As CommandBar, cbc As CommandBarControl, cbcNew As CommandBarControl
Dim cbcOpt As CommandBarControl, cbcBtn As CommandBarControl
Set cmdbr = Application.CommandBars("Worksheet Menu Bar")
Set cbc = cmdbr.Controls.Add(Type:=msoControlPopup, temporary:=True)
With cbc
    .Caption = "&BigDelGooner"
    .Visible = True
    .Tag = "BigDelGooner"
    .TooltipText = "Example menu for BigDelGooner"
    .Move before:=cmdbr.Controls.Count - 1
End With
    
    Set cbcNew = CreateControl(cbc, "Main Menu &1", "", msoControlPopup)
        Set cbcOpt = CreateControl(cbcNew, "Sub-menu &1", "This is a sub-menu", msoControlPopup)
            Set cbcBtn = CreateControl(cbcOpt, "Button &1", "This is a button on a sub-menu", msoControlButton)
            Set cbcBtn = CreateControl(cbcOpt, "Button &2", "This is a button on a sub-menu", msoControlButton)
        Set cbcOpt = CreateControl(cbcNew, "Sub-menu &2", "This is a sub-menu", msoControlPopup)
            Set cbcBtn = CreateControl(cbcOpt, "Button &1", "This is a button on a sub-menu", msoControlButton)
            Set cbcBtn = CreateControl(cbcOpt, "Button &2", "This is a button on a sub-menu", msoControlButton)
        Set cbcOpt = CreateControl(cbcNew, "Sub-menu &3", "This is a sub-menu", msoControlPopup)
            Set cbcBtn = CreateControl(cbcOpt, "Button &1", "This is a button on a sub-menu", msoControlButton)
            Set cbcBtn = CreateControl(cbcOpt, "Button &2", "This is a button on a sub-menu", msoControlButton)
    
    Set cbcNew = CreateControl(cbc, "Main Menu &2", "", msoControlPopup)
        Set cbcOpt = CreateControl(cbcNew, "Sub-menu &1", "This is a sub-menu", msoControlPopup)
            Set cbcBtn = CreateControl(cbcOpt, "Button &1", "This is a button on a sub-menu", msoControlButton)
            Set cbcBtn = CreateControl(cbcOpt, "Button &2", "This is a button on a sub-menu", msoControlButton)
        Set cbcOpt = CreateControl(cbcNew, "Sub-menu &2", "This is a sub-menu", msoControlPopup)
            Set cbcBtn = CreateControl(cbcOpt, "Button &1", "This is a button on a sub-menu", msoControlButton)
            Set cbcBtn = CreateControl(cbcOpt, "Button &2", "This is a button on a sub-menu", msoControlButton)
        Set cbcOpt = CreateControl(cbcNew, "Sub-menu &3", "This is a sub-menu", msoControlPopup)
            Set cbcBtn = CreateControl(cbcOpt, "Button &1", "This is a button on a sub-menu", msoControlButton)
            Set cbcBtn = CreateControl(cbcOpt, "Button &2", "This is a button on a sub-menu", msoControlButton)
End Sub
Function CreateControl(container As Variant, strCap As String, strTip As String, lngType As MsoControlType, Optional tagLine, Optional Macro) As CommandBarControl
    Dim ctrl
    Set ctrl = container.Controls.Add(lngType)
    With ctrl
        .Caption = strCap
        .TooltipText = strTip
        If Not IsMissing(tagLine) Then .Tag = tagLine
        If Not IsMissing(Macro) Then .OnAction = Macro
    End With
    Set CreateControl = ctrl
End Function

 

[BigDelGooner]

Richard this is excellent, thank you very much for your help!