Command and Menu Bars
Charlie Kindschi
Microsoft Corporation
March 1999
Summary: Describes how to programmatically create and remove custom toolbars using the CommandBar object model included in the Microsoft® Office 8.0 object library. (21 printed pages)
Note This article applies to Microsoft Access 97 and later.
Contents
Introduction
Why Code Command Bars?
Create a Custom Toolbar
Add Another Control
Remove the Toolbar
Work with Built-in Menu Bars
Create a New Menu
How It Works
Using the Tag and OnAction Properties Together
Remove Demo Menu
Create Shortcut Menus on Forms
User-Proofing Custom Command Bars
User-Proofing Command Bar Code
Where to Go from Here
Introduction
The Microsoft Office 8.0 object library contains a CommandBar object model that is shared across the Office suite of products. The CommandBar object model includes menu bars, toolbars, and pop-up or shortcut menus. It can be used to manipulate the built-in toolbars and menu bars and to create new ones.
This article shows you the following:
- How to programmatically create and remove a custom toolbar, complete with command buttons and drop-down lists
- How to code the procedure, called by the OnAction property, to handle controls such as drop-down lists that contain many choices
- How to add and remove menu items from the built-in Office menus
- How to create new menus that contain cascading menus of their own
- How to create and use pop-up menus as right-click (shortcut) menus on Access forms
- How to "user-proof" your command and menu bars from changes that may be made through the user interface
Why Code Command Bars?
It is not immediately obvious why you should want to create and manipulate command bars by using Microsoft Visual Basic® for Applications (VBA) and the CommandBars object model, inasmuch as the same end can be accomplished through the interface.
The reasons for coding in the CommandBars object model are twofold:
- First, VBA coding gives you a finer degree of control than can be achieved through the interface.
- Second, you achieve increased fault tolerance through the use of VBA coding. For example, suppose you build an application whose function depends on the existence of a particular command bar. Then suppose a user deletes this control. The application is rendered useless. If the control was created through the interface, you will have to fix it manually. If it was created by using VBA procedures and the CommandBars object model, the application will re-create the control and, in effect, "heal" itself the next time the object requiring it (form, report, document, or worksheet) is run.
Create a Custom Toolbar
This section shows you how to use Visual Basic for Applications and the CommandBars object model to create a custom tool bar with one command button that calls a procedure. The following steps create a custom toolbar programmatically.
Note Code examples in this article work just as well in Microsoft Word, Excel, or PowerPoint® code modules. They are presented in Access in order to illuminate some small variations regarding the use of command bars. Access makes an admirable test bed for command bar code because, unlike the other Office applications, each time you click a custom command bar, focus is not shifted to the application window.
- In Access, open a new, blank database. Open a new code module by clicking New on the Modules tab of the database window.
- Set a reference to the Microsoft Office 8.0 Object Library. (On the Tools menu, click References, and then place a check mark next to Microsoft Office 8.0 Object Library.)
Note This reference is set by default in Word, PowerPoint, and Excel, but must be set manually in Access.
- Paste or type in the NewToolbar and DisplayMessage procedures, shown here, into the code module:
Sub NewToolBar()
Dim cbrCommandBar As CommandBar
Dim cbcCommandBarButton As CommandBarButton
' If the command bar exits, remove it.
On Error Resume Next
Application.CommandBars("My CommandBar").Delete
' Add the command bar to the application's
' CommandBars collection.
Set cbrCommandBar = _
Application.CommandBars.Add
cbrCommandBar.Name = "My CommandBar"
' Add command button control to the control's
' collection of CommandBar objects.
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
' Set properties of the command button.
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = "My Big Button"
.FaceId = 19
.TooltipText = _
"Press me for fun and profit."
.OnAction = "DisplayMessage"
.Tag = "My Big Button"
End With
End With
cbrCommandBar.Visible = True
End Sub
Sub DisplayMessage()
MsgBox "My Big Button works!"
End Sub
- From the File menu, click Save (or press CTRL+S). The name you choose for the module is irrelevant to the examples provided.
- Place the cursor anywhere within the NewToolbar procedure and press F5 to run the code. You should see the following command bar.
"My Big Button" command bar
The new command bar named "My CommandBar" is created and made visible. If you click its command button, the DisplayMessage procedure is run, displaying a message box indicating what control was clicked (that is, "My Big Button works!").
Note The code examples in this article are written to expose the task of programming in the CommandBars object model as simply and clearly as possible. They are not intended to represent optimized production code. For ideas on "bullet-proofing" your command bar code for real-world applications, see the section later in this article titled "User-Proofing Command Bar Code."
Add Another Control
This section shows you how to add a list box to a custom command bar and how to code the OnAction property to react to the many choices this type of control presents.
- Below the two Dim statements at the top of the NewToolBar procedure, add this:
Dim cbcCommandBarListBox As CommandBarComboBox
- Add the following lines of code just below the block of code beginning with the comment "Set properties of the command button."
' Set properties of the drop-down list box.
Set cbcCommandBarListBox = _
.Add(Type:=msoControlDropdown)
With cbcCommandBarListBox
.AddItem " Report"
.AddItem " Form"
.AddItem " Chart"
.ListIndex = 2
.Caption = "Print"
.Style = msoComboLabel
.BeginGroup = True
.OnAction = "DisplayMessage"
.Tag = "lstPrint"
End With
- Place the insertion point anywhere within the NewToolbar procedure and press F5. You should see this command bar. (Click the down arrow to see the drop-down list.)
"My CommandBar" with two controls
Of course, clicking any of the items in the new list box generates the same simple message that clicking "My Big Button" displays: "My Big Button works!" You will see how to remedy this in the next section, "OnAction and the ActionControl."
OnAction and the Action Control
To properly handle the increased number of choices enabled by the addition of a list box to the "My CommandBar" command bar, the procedure called by the OnAction property will have to be changed. Replace the three-line DisplayMessage procedure shown earlier with the following code:
Function DisplayMessage()
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars.ActionControl
' Determine which control was clicked and act on it.
With ctl
If .Tag = "lstPrint" Then
Select Case Trim(ctl.Text)
Case "Report"
MsgBox "The User Chose " & .Text
Case "Form"
MsgBox "The User Chose " & .Text
Case "Chart"
MsgBox "The User Chose " & .Text
End Select
Else
If .Caption = "My Big Button" Then
MsgBox "The Button pressed was " _
& ctl.Caption
End If
End If
End With
End Function
Because CommandBar controls do not have Click event procedures, the ActionControl property is a very important part of command bar programming. The DisplayMessage procedure is called by the OnAction property of both controls on the "My CommandBar" command bar. A variable (ctl) is declared as the generic CommandBarControl type. It can represent any control type that resides on a command bar. The ActionControl property of the CommandBars object returns the command bar control object whose OnAction property called the procedure that is running. It lets you know which control on the command bar was clicked.
By interrogating the Tag property of the control returned by the ActionControl property, you can determine which of the two controls was clicked. If the Tag property of the control is "lstPrint," the list box was clicked. By interrogating the Text property of the drop-down list box, you can determine the user's choice. This example simply displays a message box with the text of the item selected. It would be just as easy to call separate procedures for each choice available in the list box.
If the Tag property of the control is not "lstPrint," the user must have clicked the only other choice, the "My Big Button" command button. Just as with the list box, a procedure could be called to perform any functionality appropriate to this command button.
Remove the Toolbar
Before going any further you would no doubt like to know how to get this test toolbar out of your application.
- Place the following procedure below the other procedures in the code module:
Sub RemoveCommandBar()
On Error Resume Next
Application.CommandBars("My CommandBar").Delete
End Sub
- Place the insertion point within the RemoveCommandBar procedure and press F5. The "My CommandBar" command bar will be deleted.
This removal procedure is easy to understand but is vulnerable to user interference. If a user changes the name property of "My CommandBar," the procedure will fail. In the section of this article titled "User-Proofing Command Bar Code," you will see how to rectify this situation by using the FindControl method of the CommandBar object.
It is good coding practice to remove any command bars created by your code procedures when users leave the environment. In Access, call the RemoveCommandBar procedure from the form Close or report Unload events. In Excel, use the workbook BeforeClose event; in Word the document Close event.
Work with Built-in Menu Bars
Menu bars and toolbars look different, but in the eyes of the CommandBars object model, they are very much the same. Menu bars are just a special kind of toolbar. For example, the toolbar named "Menu Bar" contains several CommandBarPopup-type controls with names such as File, Edit, and View.
This section of the article shows you how to add an item to a built-in Office menu, how to remove it, and how to handle its OnAction property. You will also see a small variation needed to programmatically add menu items to your Excel-based projects.
- Key in or paste the following two procedures into the Access code module you have been working in. (As with the preceding code example, this procedure will fail if the user changes the name property of the subject control from "Menu Bar". It is easier to understand the concept of creating new menu items if we deal with this contingency in the section titled "User-Proofing Command Bar Code" rather than including the more abstract code here.)
Sub AddMenuItem()
Dim cbmMenuBar As CommandBarPopup
Dim cbcMenuItem As CommandBarButton
Dim strMenuBarName As String
' Remove menu item if it exists.
On Error Resume Next
Application.CommandBars("Menu Bar") _
.Controls("File") _
.Controls("My New Menu Item") _
.Delete
' Identify menu bar that will receive new item.
strMenuBarName = "Menu Bar"
Set cbmMenuBar = Application _
.CommandBars(strMenuBarName) _
.Controls("File")
' Add menu item.
Set cbcMenuItem = cbmMenuBar.Controls _
.Add(Type:=msoControlButton, Before:=4)
' Set property values of menu item.
With cbcMenuItem
.Caption = "My New Menu Item"
.OnAction = "DisplayMessage"
.Tag = "My New Menu Item"
End With
End Sub
- To try the example code, place the insertion point within the AddMenuItem procedure and press F5 to run it. Look for the new item ("My New Menu Item") in the fourth position on the File menu.
- Add the following Select Case statement to the DisplayMessage procedure, below the If...Then decision block but within the With ctl statement. There is only the single case now, but it will be used to react to the rest of the controls added in this article.
Select Case ctl.Tag
Case Is = "My New Menu Item"
MsgBox "The User clicked " _
& .Caption
End Select
Menu bars in Excel
There is a slight variation to the code you have seen so far when creating custom menu items in Excel. The built-in menu bar default name is "Worksheet Menu Bar", rather than simply Menu Bar, as it is in Word, Access, and PowerPoint. When working in Excel, you can simply use Worksheet Menu Bar as the default name or you can add the following Select statement to make your menu-bar code generic to Word, Excel, or PowerPoint. (The Access application object has no Name property so the following code will not compile in that environment.)
Select Case Application.Name
Case "Microsoft Excel"
strMenuBarName = "Worksheet Menu Bar"
Case Else
strMenuBarName = "Menu Bar"
End Select
Remove Menu Item
In the section earlier in this article titled "Remove the Toolbar," we used the Name property to identify the control to be deleted. When you are deleting command bar controls, such as menu items, the Tag property provides a more "user-proof" way of identifying controls. Users can change the Name property of your custom menu items and control bars through the interface by clicking Customize from the View menu. If your code relies on a property that users have easy access to, you're sure to encounter problems. Inasmuch as the Tag property is less accessible to users, it is a better choice than the Name or Caption properties, when it is available. (There is no Tag property for the top-level command bar control. This issue is addressed in the section titled "User-Proofing Command Bar Code.")
Type or paste the following procedures in the Access code module you have been working in:
Sub RemoveMenuControl(strItemToRemove As String)
Dim ctl As CommandBarControl
On Error Resume Next
Set ctl = Application.CommandBars _
.FindControl(, , Tag:=strItemToRemove)
ctl.Delete
End Sub
Sub UseRemoveMenuControl()
Call RemoveMenuControl("My New Menu Item")
End Sub
The heart of the RemoveMenuControl procedure is the FindControl method of the CommandBars collection. The FindControl method returns a CommandBarControl object that fits the specified criteria. One of these criteria can be the value of a Tag property. If in any of the CommandBars Controls collections there exists a control that has a Tag property set to the value passed to the RemoveMenuControl procedure, it will be deleted. Because the Tag property of your custom controls can be made inviolate to users (through password protection), the FindControl method provides a way to create "bullet-proof" control deletion procedures.
Note As you create custom controls, it is important that you use unique values for their Tag properties or unexpected results could occur.
The UseRemoveMenuControl procedure is provided simply as an easy way to use the RemoveMenuControl procedure. By passing the tag value "My New Menu Item," the menu item created in this portion of the article can be removed even if a user changes its caption property. As you will see later in this article, the RemoveMenuControl procedure can be used to remove any command bar control that has a Tag property value set.
Create a New Menu
In this section of the article you will see how to create a new menu on the menu bar and how to employ the Tag property of controls to get proper results from the procedure called by a control's OnAction property. This is useful when your application contains many options and you do not wish to expand the built-in menus (such as File and Edit).
- Add the following code to the Access module you've been working in:
Sub AddMenuCascade()
Dim cbmCommandBarMenu As CommandBar
Dim cbmDemoMenu As CommandBarPopup
Dim cbmCommandBarMenuCascade As CommandBarPopup
' Clear the way for new menu.
On Error Resume Next
Application.CommandBars("Menu Bar") _
.Controls("&Demo Menu").Delete
' Identify built-in menu bar to work with.
Set cbmCommandBarMenu = Application _
.CommandBars("Menu Bar")
' Add the new menu.
With cbmCommandBarMenu.Controls
Set cbmDemoMenu = _
.Add(Type:=msoControlPopup, Before:=3)
' Set caption for new menu.
With cbmDemoMenu
.Caption = "&Demo Menu"
' Add single menu item and set properties.
With .Controls.Add(msoControlButton)
.OnAction = "DisplayMessage"
.Caption = "Demo &Menu Item"
.Tag = "DemoMenuItem"
End With
End With
' Add cascading menu and set properties.
Set cbmCommandBarMenuCascade = _
cbmDemoMenu.Controls.Add(msoControlPopup)
With cbmCommandBarMenuCascade
.Caption = "Demo Cascade"
' Add first cascading menu item and set properties.
With .Controls.Add(msoControlButton)
.Caption = "&Cascade Item 1"
.OnAction = "DisplayMessage"
.Tag = "Cascade Item 1"
End With
' Add second cascading menu item and set properties
With .Controls.Add(msoControlButton)
.Caption = "Cascade I&tem 2"
.OnAction = "DisplayMessage"
.Tag = "Cascade Item 2"
End With
End With
End With
End Sub
- Place the insertion point within the AddMenuCase procedure and press F5 to run it. Your Access menu bar should now have another menu after the File and Edit menus called Demo Menu.
Demo Menu added to built-in menu bar
How It Works
Where menus are located is dependent on which Controls collection they are added to. The menu item you saw in the AddMenuItem procedure added a control to the Controls collection of the File menu. The AddMenuCascade procedure is designed to add a new menu on the same level as the File menu. In the AddMenuCascade procedure, the Demo Menu is added to the Controls collection of the Menu Bar command bar. This is the same collection that contains the File menu, so they are on the same level. Two menu items are added to the Controls collection of the Demo Menu: Demo Menu Item and Demo Cascade. Adding controls to the Demo Cascade menu item's Controls collection creates the pop-up menu containing "Cascade Item1" and "Cascade Item2."
Using the Tag and OnAction Properties Together
If you click on either of the items in the Demo Menu you may be disappointed to find that nothing happens. The controls do not meet any of the criteria tested for in the DisplayMessage procedure. The code in previous examples polled the Tag property to do various things, based on which item was clicked.
The Tag property returns or sets information about CommandBar controls. It is a read/write string that can be used to identify particular controls on a command bar. When you use it in conjunction with the ActionControl property of the CommandBars collection, you can call a relevant procedure for any custom menu item from the procedure called by the OnAction property. Each command button in the "Demo Menu" menu bar has a value given to its Tag property.
Replace the second Select Case statement in the DisplayMessage procedure with the following code. Try clicking the various buttons in the Demo Menu and note the result.
Select Case ctl.Tag
Case Is = "My New Menu Item"
MsgBox "The User clicked " _
& .Caption
Case Is = "DemoMenuItem"
MsgBox "The User clicked " _
& .Caption
Case Is = "Cascade Item 1"
MsgBox "The User clicked " _
& .Caption
Case Is = "Cascade Item 2"
MsgBox "The User clicked " _
& .Caption
End Select
Remove Demo Menu
Removing menus is very similar to removing menu items. The UseRemoveMenuControl procedure works in the same manner as shown earlier in the section about removing a menu item. To use it, simply call the RemoveDemoMenu procedure and pass the Tag property value of the menu to be removed. For example, to remove the Demo Menu, paste in the procedures shown here and run them:
Sub RemoveDemoMenu(strTagValue As String)
Dim cbmDemoMenu As CommandBarPopup
On Error Resume Next
Set cbmDemoMenu = CommandBars.FindControl _
(, , Tag:=strTagValue)
cbmDemoMenu.Delete
End Sub
Sub UseRemoveDemoMenu()
Call RemoveDemoMenu("Demo Menu")
End Sub
The advantage of using this cleanup method is that it works even if a user has changed the Parent control bar's Name property. See "User-Proofing Command Bar Code" later in this article for more information.
Create Shortcut Menus on Forms
Pop-up menus (sometimes called cascading menus) are simply menus that are not part of any parent object's Controls collection. They make excellent right-click (shortcut) menus for Office applications.
Creating a shortcut menu is slightly different from creating standard command bars or menus. To create a shortcut or pop-up menu, you must set the Position argument of the Add method of the CommandBars object to the MsoBarPosition constant, msoBarPopup.
To create a pop-up or shortcut menu, add the following procedure to the Access code module you have been working in and run it:
Sub AddShortCut()
Dim cbcMenuItem As CommandBarButton
Dim cbpShortCut As CommandBar
On Error Resume Next
Application.CommandBars("myShortCutMenu").Delete
Set cbpShortCut = CommandBars _
.Add(, Position:=msoBarPopup)
cbpShortCut.Name = "myShortCutMenu"
With cbpShortCut.Controls
Set cbcMenuItem = .Add(msoControlButton)
With cbcMenuItem
.Caption = "Print Report"
.Style = msoButtonIconAndCaption
.FaceId = 125
.OnAction = "DisplayMessage"
.Tag = "Print Report"
End With
Set cbcMenuItem = .Add(msoControlButton)
With cbcMenuItem
.Caption = "Print Chart"
.Style = msoButtonIconAndCaption
.FaceId = 17
.OnAction = "DisplayMessage"
.Tag = "Print Chart"
End With
End With
End Sub
To see the new pop-up menu in Access
- Right-click any Access toolbar and click Customize from the choices provided to open the Customize dialog box.
- In the Toolbars tab of the Customize dialog box, double-click Shortcut Menus to open the Shortcut Menus menu bar.
- Click the Custom menu on the Shortcut Menus menu bar.
To use the shortcut menu with an Access form
- Press F11 to open the database window.
- Create a new unbound form: Click the Forms tab, click New, and then click OK on the New Form dialog box.
- On the form's property sheet, set the Name property to frmShortCut.
- Optionally, add a label control and change its Caption property to Right Click Me.
- Change the form to run view and right-click within the form.
Access form with shortcut menu
Set Shortcut Menus in Code
If you have several shortcut menus and you wish to change a form or other control's shortcut menu on the Applies To list, you can do it programmatically. The following subprocedure sets the ShortcutMenuBar property of the form named frmShortcut:
Sub SetShortCutMenu()
Dim frm As Form
Set frm = Application.Forms!frmShortcut
frm.ShortcutMenuBar = "myShortCutMenu"
End Sub
Under certain circumstances you may wish to toggle the availability of shortcut menus on Access forms. You can do so by changing the Boolean ShortCutMenu property. Setting the property to True (default) makes a shortcut menu available, if one is set for the ShortCutMenuBar property.
Microsoft User forms and Access forms are part of different form sets. Office User forms do not support the Right-click event or the ShortCutMenuBar property. To use shortcut menus with Office user forms you must employ the ShowPopUp method of the CommandBar object and the MouseDown event of the user form. Go through the following steps to use a shortcut menu with a User form in Excel. The example will be hosted in Excel but will work just as well in Word or PowerPoint. In Excel do the following:
- Open the Visual Basic Editor by clicking Alt-F11.
- Insert a new code module by clicking Module on the Insert menu.
- Key in or paste the AddShortCut procedure, shown earlier.
- Add the following line of code just above the End Sub statement and below the last End With statement:
cbpShortCut.ShowPopup 200, 200
- Insert a new UserForm by clicking UserForm on the Insert menu.
- Add the following code to the MouseDown event of the user form:
Private Sub UserForm_MouseDown( _
ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, ByVal Y _
As Single)
'Check for Right mouse button was clicked
If Button = 2 Then
Call Module1.AddShortCut
End If
End Sub
- Add this simple procedure to the code module to test the OnAction property of the controls:
Sub DisplayMessage()
MsgBox "Popup Control Worked"
End Sub
Remove Shortcut Menu
Proper coding practice requires that if you change something about an application via code you change it back when you are done using it. The following procedure is called from the form's Close event (Access) or the Deactivate event (User Form) it will remove the shortcut menu created earlier.
Sub RemoveShortCuts()
On Error Resume Next
Application.CommandBars _
("myShortCutMenu").Delete
End Sub
This procedure is subject to the same vulnerability as the RemoveCommandBar procedure. If a user changes the name of the subject control, the procedure fails. Because users can access the Name property through the interface, this contingency is a very real possibility. The next section of this article shows you how to use the FindControl method of the CommandBars object to user-proof your code.
User-Proofing Custom Command Bars
The Problem
To visualize the problem that user tampering can visit upon command bar code, try the following:
- If the command bar named "My CommandBar" is not visible, run the NewToolbar procedure.
- Open the Customize dialog box by right-clicking "My CommandBar" and clicking Customize from the shortcut menu.
- Open the Toolbar Properties dialog box by clicking "My CommandBar" in the Toolbars tab and then clicking Properties.
- Type "Your CommandBar" in the Toolbar Name text box and click Close twice.
"My CommandBar" with name property changed
If you try to remove the command bar by using the RemoveCommandBar procedure given earlier, nothing happens. There is no "My CommandBar" to remove. This is not really such a tragedy. Because the NewToolbar procedure will be run the next time your application opens, the only real harm is that an extra "Your CommandBar" will be cluttering up the fairway, but because we can fix the problem, we must fix the problem. The RemoveCommandBar procedure is shown for reference:
Sub RemoveCommandBar()
On Error Resume Next
Application.CommandBars("My CommandBar").Delete
End Sub
The Solution
Please add the following three procedures to the Access code module you have been working in and read on for an explanation of how they work to clean up command bars even if users change the values of name properties.
Function RemoveBars(strTagName As String)
Dim cmdBar As CommandBar
Dim cmdBarCtl As CommandBarControl
Dim strCBarName As String
On Error Resume Next
'Use Find Control with Tag value passed in
Set cmdBarCtl = Application.CommandBars _
.FindControl(msoControlButton, , _
Tag:=strTagName)
'Determine which command bar it resides on
Set cmdBar = cmdBarCtl.Parent
'Set the Name property of that command bar
strCBarName = cmdBar.Name
'Delete the bar
Call RemoveCommandBars(strCBarName)
End Function
Sub RemoveCommandBars(strToRemove As String)
On Error Resume Next
Application.CommandBars(strToRemove).Delete
End Sub
The Delete method of the CommandBar object requires that a particular member of the CommandBars collection be identified. Use CommandBars(index), where index is the name or index number of a command bar, to return a single CommandBar object. Because the index number of a CommandBar object is not fixed and the Name property may be changed through the interface, another way must be found to identify a custom command bar in order to delete it. (Of course you could loop through the CommandBars collection and delete all those that are not built in, but that is not a very elegant solution.)
The way to identify a particular custom command bar, if you can not be sure of its name, is through its controls. For example, we know that that the command bar formerly named "My CommandBar" has a command button with a Tag property set to "My Big Button." Please examine the following fragment of the RemoveBars function:
' Use Find Control with Tag value passed in.
Set cmdBarCtl = Application.CommandBars _
.FindControl(msoControlButton, , _
Tag:=strTagName)
The cmdBarCtl variable can represent any type of CommandBar control. The strTagName variable is passed into the RemoveBars function and represents the value of the Tag property of a control in the Controls collection of a command bar in the application. The FindControl method of the CommandBars object returns a CommandBarControl object that fits the specified criteria. In this case the criterion is a Tag property value. So, if the Tag property value "My Big Button" is passed into the RemoveBars function, the cmdBarCtl variable will represent a control on the command bar we are trying to delete. By using the Parent property of that command bar, we are able to set the cmdBar variable to our target CommandBar object. The final step is to pass the name of the target bar to the RemoveCommandBars procedure, which will delete the target command bar no matter what the user may have named it. (If the user deleted "My Big Button" the procedure is still in trouble but you get the idea.)
' Determine which command bar it resides on.
Set cmdBar = cmdBarCtl.Parent
' Set the Name property of that command bar.
strCBarName = cmdBar.Name
' Delete the bar.
Call RemoveCommandBars(strCBarName)
To remove the Your CommandBar command bar and the myShortcutMenu menu bar, add the following procedure to the code module you have been working in and run it:
Sub CleanUp()
Call RemoveBars("My Big Button")
Call RemoveBars("Print Report")
End Sub
The target command bars will be deleted.
User-Proofing Command Bar Code
The code for the AddMenuItem and AddMenuCascade procedures both rely on the user not changing the name of menu bar. The FindControl method of the CommandBars object can help break this tenuous reliance.
User-proof the AddMenuItem procedure
To understand how this is possible, please take a look at the following code snippet from the AddMenuItem procedure, shown earlier in this article.
' Identify menu bar to receive new item.
strMenuBarName = "Menu Bar"
Set cbmMenuBar = Application _
.CommandBars(strMenuBarName) _
.Controls("File")
The cbmMenuBar variable is declared with its type set to CommandBarPopUp so that it can represent the built-in pop-up menu named File. The preceding code snippet is relying on the Controls collection of a command bar named Menu Bar to contain the File control. If the Menu Bar control bar is renamed by the user, the procedure will fail. What is required is a way to reference the File pop-up menu control even if its container control bar is renamed. Fortunately, all the built-in pop-up menu controls have an ID property that can be used as a type argument to the FindControl method. The following two code snippets are equivalent:
' Identify menu bar to receive new item.
Set cbmMenuBar = CommandBars.FindControl(, _
Id:=30002)
' Identify menu bar to receive new item.
strMenuBarName = "Menu Bar"
Set cbmMenuBar = Application _
.CommandBars(strMenuBarName) _
.Controls("File")
If you would like to see this for yourself, replace code in the AddMenuItem procedure to use the ID number for the File menu and run the procedure.
Find the numbers
The following procedure is provided as an example of how to find ID number values for built-in menus. The example yields 30004, the ID property value for the View menu.
Sub FindIdNumber()
Dim cbrBar As CommandBar
Dim cbcMenuBar As CommandBarPopup
Set cbrBar = CommandBars("Menu Bar")
Set cbcMenuBar = cbrBar.Controls("View")
With cbcMenuBar
MsgBox "The ID Number for the " _
& .Caption & " Control is " & .Id
End With
End Sub
User-proof the AddMenuCascade procedure
To understand how to protect this procedure, please examine the following code snippet from the AddMenuCascade procedure shown earlier in this article:
' Clear the way for new menu.
On Error Resume Next
Application.CommandBars("Menu Bar") _
.Controls("&Demo Menu").Delete
' Identify built-in menu bar to work with.
Set cbmCommandBarMenu = Application.CommandBars("Menu Bar")
' Add the new menu.
With cbmCommandBarMenu.Controls
Set cbmDemoMenu = _
.Add(Type:=msoControlPopup, Before:=3)
As you can see, if the name "Menu Bar" is changed, the AddMenuCascade procedure will fail. This problem is removed by using the FindControl method, as shown here:
' Clear the way for new menu.
On Error Resume Next
Set cbmDemoMenu = CommandBars.FindControl _
(, , Tag:="Demo Menu")
cbmDemoMenu.Delete
' Identify built-in menu bar to work with.
Set cbmDemoMenu = CommandBars.FindControl (, Id:=30004)
Set cbmCommandBarMenu = cbmDemoMenu.Parent
strBarName = cbmCommandBarMenu.Name
Set cbmCommandBarMenu = Application.CommandBars(strBarName)
' Add the new menu.
With cbmCommandBarMenu.Controls
Set cbmDemoMenu = _
.Add(Type:=msoControlPopup, Before:=3)
The cmdDemoMenu variable is typed as a CommandBarPopup. The Tag property of the FindControl method is used to detect if Demo Menu exists. If it does, it is deleted without reference to the command bar, default name "Menu Bar." Next, the cmdDemoMenu variable is used to gain a reference to the built-in View menu. The ID property of the View menu is 30004. Because the View menu is contained in the Controls collection of the target command bar, we are able to use its Parent property to set the value of the cbmCommandBarMenu variable (typed CommandBar) without referring to the default name "Menu Bar." Thus the task of adding a menu to the command bar with the default name of "Menu Bar" is accomplished even if that name has been altered.
Where to Go from Here
If you got this far you have seen how to add a new menu item and new menu or command bars to Office applications. You know how to handle multiple response controls such as combo boxes. You've seen how to handle changes to default settings that users can make through the interface.
More References