Add PopupControl to CommandBar : CommandBar « Application « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Application
3. CommandBar

Add PopupControl to CommandBar

 
Sub AddNewMB()
   Dim myCommandBar As CommandBar, myCommandBarCtl As CommandBarControl
   Dim myCommandBarSubCtl As CommandBarControl
   On Error GoTo AddNewMB_Err

   Set myCommandBar = CommandBars.Add(Name:="Sample Menu Bar", Position:= _
      msoBarTop, MenuBar:=True, Temporary:=False)
   myCommandBar.Visible = True
   myCommandBar.Protection = msoBarNoMove

   Set myCommandBarCtl = myCommandBar.Controls.Add(Type:=msoControlPopup)
   myCommandBarCtl.Caption = "Displa&y"

   Set myCommandBarSubCtl = myCommandBarCtl.Controls.Add(Type:=msoControlButton)
   With myCommandBarSubCtl
      .Style = msoButtonIconAndCaption
      .Caption = "E&nable ClickMe"
      .FaceId = 59
      .OnAction = "=ToggleClickMe()"
      .Parameter = 1
      .BeginGroup = True
   End With
AddNewMB_Err:
   msgBox "Error " & Err.number & vbCr & Err.Description
   Exit Sub
End Sub
Function ToggleClickMe()
   Dim MyMenu As CommandBar
   Dim myCommandBarClickMe As CommandBarControl
   On Error GoTo ToggleClickMe_Err
   Set MyMenu = CommandBars("Sample Menu Bar")
   Set myCommandBarClickMe = MyMenu.Controls(2)
   With CommandBars.ActionControl
      Select Case .Parameter
         Case 1
            myCommandBarClickMe.Enabled = True
         Case 2
            myCommandBarClickMe.Enabled = False
      End Select
   End With
   Exit Function
ToggleClickMe_Err:
   msgBox "Error " & Err.number & vbCr & Err.Description
   Exit Function
End Function

 

Related examples in the same category