Menus

Again, with a few variations, the little demonstration in this section is complements of some friends of mine at Microsoft. Like before, it shows some very interesting features. Here is the code:

Sub AddNewMB()   Dim MBar As CommandBar, MBarCtl As CommandBarControl   Dim MBarSubCtl As CommandBarControl   On Error GoTo AddNewMB_Err   ' Create a new menu bar and dock it on the left.   Set MBar = CommandBars.Add(Name:="Sample Menu Bar", Position:= _   msoBarTop, MenuBar:=True, Temporary:=False)   ' Make the menu bar visible.   MBar.Visible = True   ' Prevent users from undocking the menu bar.   MBar.Protection = msoBarNoMove   ' Create a popup control on the bar and set its caption.   Set MBarCtl = MBar.Controls.Add(Type:=msoControlPopup)   MBarCtl.Caption = "Displa&y"   ' Create 2 controls on the Display popup and set some properties.   Set MBarSubCtl = MBarCtl.Controls.Add(Type:=msoControlButton)   With MBarSubCtl   .Style = msoButtonIconAndCaption   .Caption = "E&nable ClickMe"   .FaceId = 59   .OnAction = "=ToggleClickMe()"   .Parameter = 1   .BeginGroup = True   End With   Set MBarSubCtl = MBarCtl.Controls.Add(Type:=msoControlButton)   With MBarSubCtl   .Style = msoButtonIconAndCaption   .Caption = "Di&sable ClickMe"   .FaceId = 276   .OnAction = "=ToggleClickMe()"   .Parameter = 2   .BeginGroup = True   End With   ' Add another control to the menu bar.   Set MBarCtl = MBar.Controls.Add(Type:=msoControlButton)   With MBarCtl   .BeginGroup = True   .Caption = "&ClickMe"   .Style = msoButtonCaption   .OnAction = "=MsgBox(""You clicked ClickMe"")"   End With   ' Add a control to make this menu bar invisible and bring back   ' the system menu bar.   Set MBarCtl = MBar.Controls.Add(Type:=msoControlButton)   With MBarCtl   .BeginGroup = True   .Caption = "&Set Visibility Off"   .Style = msoButtonCaption   .OnAction = "=SampleMenuDisable()"   End With   Exit Sub AddNewMB_Err:   MsgBox "Error " & Err.Number & vbCr & Err.Description   Exit Sub End Sub '**************************************************************** ' This procedure uses the Parameter property of a command bar ' control to execute a different action depending on which item ' you click on a popup menu. '**************************************************************** Function ToggleClickMe()   Dim MyMenu As CommandBar   Dim MBarClickMe As CommandBarControl   On Error GoTo ToggleClickMe_Err   Set MyMenu = CommandBars("Sample Menu Bar")   Set MBarClickMe = MyMenu.Controls(2)   ' The ActionControl property of command bars returns the control   ' whose OnAction property is running this procedure.   With CommandBars.ActionControl   Select Case .Parameter   Case 1   MBarClickMe.Enabled = True   Case 2   MBarClickMe.Enabled = False   End Select   End With   Exit Function ToggleClickMe_Err:   MsgBox "Error " & Err.Number & vbCr & Err.Description   Exit Function End Function '**************************************************************** ' This function restores the original menu bar. Because there can ' only be one system menu bar, you must hide the sample menu bar ' when you want to bring back the previous system menu bar. '**************************************************************** Function SampleMenuDisable()   Application.CommandBars("Sample Menu Bar").Visible = False   Application.CommandBars("Menu Bar").Visible = True End Function

If you run the original procedure, you end up with the menu bar, shown here.

In the code, you create the menu bar as follows:

Set MBar = CommandBars.Add(Name:="Sample Menu Bar", Position:= _   msoBarTop, MenuBar:=True, Temporary:=False)   ' Make the menu bar visible.   MBar.Visible = True   ' Prevent users from undocking the menu bar.   MBar.Protection = msoBarNoMove 

You add the menu bar to the CommandBars collection as you did with the toolbar before. You even set its position at msoBarTop. You have to set its visibility, as before, to true. In this case, however, you prevent the user from undocking the menu by setting the Protection property to msoBarNoMove.

Again, like before, you add controls to the menu bar. The first of these is defined as follows:

' Create a popup control on the bar and set its caption. Set MBarCtl = MBar.Controls.Add(Type:=msoControlPopup) MBarCtl.Caption = "Displa&y"

This code adds a menu item (called a CommandBarControl object) to the “Sample Menu Bar” menu bar. The menu item’s caption is “Display.”

The following code then adds another menu item (or CommandBarControl object) to the menu item whose caption is Display:

Set MBarSubCtl = MBarCtl.Controls.Add(Type:=msoControlButton)   With MBarSubCtl   .Style = msoButtonIconAndCaption   .Caption = "E&nable ClickMe"   .FaceId = 59   .OnAction = "=ToggleClickMe()"   .Parameter = 1   .BeginGroup = True   End With

This means that when the user selects the Display menu item, a drop-down menu that includes the item “Enable ClickMe” appears. (Subsequent code also adds another menu item captioned “Disable ClickMe.”) The string assigned to the OnAction property can be used to define a command or procedure that is to be executed when this menu item is selected.

Both the Enable ClickMe and Disable ClickMe items refer to the ToggleClickMe procedure. This procedure either enables or disables the ClickMe button. It retrieves a reference to the second control on the command bar (the one captioned “ClickMe”). It then looks at the value of the Parameter property, which is passed to the procedure defined by the OnAction property. Based on the value of this parameter, it decides whether to enable or disable the ClickMe button.

Even though this seems like a lot of code, based upon what you have learned already, it is pretty straightforward and easy to follow.