Programming Excel with VBA and .NET

Name

commandbarpopup.Controls

Synopsis

Returns the CommandBarControls collection for the pop-up menu. Use this collection to add or remove items from the menu. For example, the following code creates a menu on the worksheet menu bar and adds three menu items to it:

Sub AddPopupMenu( )
    Dim cb As CommandBar, cpop As CommandBarPopup
    ' Get a command bar
    Set cb = CommandBars("Worksheet Menu Bar")
    ' Create the combo box.
    Set cpop = cb.Controls.Add(msoControlPopup, , , , True)
    ' Add a Tag so this control can be found from other code.
    cpop.Tag = "cpopCustomMenu"
    cpop.Caption = "&Custom Menu"
    ' Add items.
    With cpop.Controls.Add(msoControlButton, , , , True)
        .Caption = "Item &1"
        .OnAction = "Item1_Click"
        .Tag = "cbbItem1"
    End With
    With cpop.Controls.Add(msoControlButton, , , , True)
        .Caption = "Item &2"
        .OnAction = "Item2_Click"
        .Tag = "cbbItem2"
    End With
    ' Built-in command.
    With cpop.Controls.Add(msoControlButton, 1695, , , True)
        ' Add separator bar.
        .BeginGroup = True
    End With
End Sub
 
Sub Item1_Click( )
    ' Add code to respond to menu item Click Here...
End Sub
 
Sub Item2_Click( )
    ' Add code to respond to menu item Click Here...
End Sub