Microsoft Access VBA Programming for the Absolute Beginner

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.