Creating sub menu in Command bar
This was done very quickly and tested briefly. I'm short on time today.
It shows that you can use a standard commandbar button to display a popup
type toolbar (Position:=msoBarPopup) in the place of a standard menu type
control (msoControlPopup). I use a loop to populate the popup toolbar and use
arrays to apply Caption, OnAction and FaceId properties. I also show that
this method has the advantage that you can change the status (e.g. Enabled
property) of controls before displaying the popup.
I repeat, this was done quickly. My tests indicate that it's OK. Best of
luck !!!
Regards,
Greg
Sub XYZ()
Dim CBar As CommandBar, Popup As CommandBar
Dim ctrl As CommandBarControl
Dim CaptArr As Variant, MacroArr As Variant
Dim FaceIdArr As Variant
Dim i As Long
With Application
.ScreenUpdating = False
Set CBar = .CommandBars("myToolbar")
Set ctrl = CBar.Controls.Add(Temporary:=True)
With ctrl
.Caption = "Options"
.TooltipText = "Show Option list"
.OnAction = "ShowPopup"
.FaceId = 300
.Style = msoButtonIconAndCaption
End With
On Error Resume Next
.CommandBars("Options List").Delete
On Error GoTo 0
Set Popup = .CommandBars.Add(Position:=msoBarPopup, Temporary:=True)
Popup.Name = "Options List"
CaptArr = Array("Run Macro 1", "Run Macro 2", "Run Macro 3")
MacroArr = Array("Macro1", "Macro2", "Macro3")
FaceIdArr = Array(100, 101, 102)
For i = 0 To 2
Set ctrl = Popup.Controls.Add
With ctrl
.Caption = CaptArr(i)
.OnAction = MacroArr(i)
.FaceId = FaceIdArr(i)
End With
Next
CBar.Visible = True
.ScreenUpdating = True
End With
End Sub
Private Sub ShowPopup()
Dim i As Long
'This demo toggles the enabled status of the
'second button before showing the popup commandbar
With Application.CommandBars("Options List")
.Controls(2).Enabled = Not .Controls(2).Enabled
.ShowPopup
End With
End Sub
Sub Macro1()
MsgBox "You called Macro1"
End Sub
Sub Macro2()
MsgBox "You called Macro2"
End Sub
Sub Macro3()
MsgBox "You called Macro3"
End Sub
|