Add a new menubar with toggle action
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
Set myCommandBarSubCtl = myCommandBarCtl.Controls.Add(Type:=msoControlButton)
With myCommandBarSubCtl
.Style = msoButtonIconAndCaption
.Caption = "Di&sable ClickMe"
.FaceId = 276
.OnAction = "=ToggleClickMe()"
.Parameter = 2
.BeginGroup = True
End With
Set myCommandBarCtl = myCommandBar.Controls.Add(Type:=msoControlButton)
With myCommandBarCtl
.BeginGroup = True
.Caption = "&ClickMe"
.Style = msoButtonCaption
.OnAction = "=MsgBox(""You clicked ClickMe"")"
End With
Set myCommandBarCtl = myCommandBar.Controls.Add(Type:=msoControlButton)
With myCommandBarCtl
.BeginGroup = True
.Caption = "&Set Visibility Off"
.Style = msoButtonCaption
.OnAction = "=SampleMenuDisable()"
End With
Exit Sub
AddNewMB_Err:
Debug.Print 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:
Debug.Print Err.number & vbCr & Err.Description
Exit Function
End Function
Function SampleMenuDisable()
Application.CommandBars("Sample Menu Bar").Visible = False
Application.CommandBars("Menu Bar").Visible = True
End Function
Related examples in the same category