Menus In Excel97 And 2000 

 

When Excel97 was released, the entire internal structure of Command Bars (toolbars and menus) was completely changed.   The Menu Editor tool that existed in previous version of Excel was removed.   Although menu items created in earlier versions of Excel will still work in Excel97 and 2000, the procedure for creating menus has changed.  This page describes the procedures, both manually and with VBA code, for creating your own menu items.    The procedures described here are quite simple.  You should refer to the on line help or to the Office VBA Programmers Guide for more detailed information. 

These procedures will not work for creating menu items on tool bars in the Visual Basic Editor.  For information about working with menus in the VBE, click here.  Because the VBA procedures here use the CommandBar and CommandBarControl object types, you must set a reference in your VBA Project to the Microsoft Office 8 (or 9) Object Library.

Manually Creating Menu Items

You can create new menu items by simply dropping and dragging command icons on to the menu bars or tool bars where you want them to appear, and then assign code to them.

 

         First, you will need to open the Customize dialog box, which puts Excel into the Command Bar Edit mode.  To open this dialog,  select Tool Bars from the View menu, and choose Customize from the list.  Or, you can right-click on any empty part of a tool bar, and choose Customize from the pop up menu.  The Customize dialog box is show on the right.

 

 

From this dialog, choose the Commands tab, and scroll down to the Macros item in the Categories list.  Now, choose the Custom Menu Item in the Commands list, and drag this to any menu you list.  Position it in the menu list where you'd like it to appear.   Now you've created a new menu item.  

The next step is to assign a macro to your new menu item.    To do this, leave the Customize dialog open, and right click on the new menu item you created.   The commands for customizing your new menu item are on the right click pop up menu, shown below: 

 

 

First, type in a name for the menu in the Name box.  This will assign a name and caption for the menu item.  Preceding a letter in the name with a & will underline that character and make it the menu shortcut key.  If you want to assign a picture to the menu item, click the Change Button Image and select one of the built-in menu faces.  Or, you can create your own face with the Edit Button Image tool, or copy and paste the face from another existing button image with the Copy Button Image and Paste Button Image menu items. If you're going to have a face for your button, make sure you choose the Default Style selection, not the Text Only selection.  

Next, you have to assign an existing macro to the menu item.  Click the Assign Macro item, and choose an existing macro from the list.    Once you've done this, that macro will be run when you click on the menu item.  

After you've created and customized your menu item, close the Customize dialog.  Now your menu will operate in the normal manner.  

The procedure for adding a new tool to a command bar is the same, except that you should choose  Custom Button rather than Custom Menu Item from the Macros Categories list in the Customize dialog box. 

Your custom menu items and command buttons are stored in a special file called Excel.xlb (or something similar, depending on your system configuration), usually in your C:\Windows directory.   They are not stored in the workbook that happens to be active when you create them.  Therefore, they will always be available for you to use. 

 
        

 

 

Creating Menu Items With VBA Code

You can also create menu items with VBA procedures.  You will need to use to types of variables, a CommandBar and a CommandBarControl.   The CommandBar variable will be set to an entire command bar, such as the "Worksheet Menu Bar".  A command bar contains a collection called Controls, which is the set of all controls on the command bar.  For example, the Controls collection for the Worksheet Menu Bar contain a control for each menu (File menu, Edit menu, and so on).  Each of these controls can itself contains a Controls collection.  The Worksheet Menu Bar Controls collection contains a control called "Tools" which represents the Tools menu.  This control contains a Controls collection, which contains a control for each item on the Tools menu. 

A Control is represented by the CommandBarControl object, and is created by using the Add method of the Controls collection to which it is to belong.  There are two properties of the CommandBarControl object that we'll use:  the Control OnAction property, which assigns a macro to run when the menu item is clicked.   There are other properties, which are discussed in detail in the help files and in VBA books.  

The following code will illustrate these concepts.  It adds a menu item called "New Item" to the "Tools" menu, and assigns a macro called "MyMacro" to the menu item.  


Sub AddNewMenuItem()

Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl
Dim CmdBarMenuItem As CommandBarControl
'
' Point to the Worksheet Menu Bar
'
Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
'
' Point to the Tools menu on the menu bar
'
Set CmdBarMenu = CmdBar.Controls("Tools")
'
' Add a new menu item to the Tools menu
'
Set CmdBarMenuItem = CmdBarMenu.Controls.Add
'
' Set the properties for the new control
'
With CmdBarMenuItem
    .Caption = "New Item"
    .OnAction = "'" & ThisWorkbook.Name & "'!MyMacro"
     .Tag = "SomeString"
End With

End Sub

Notice that when we assign the macro name to the menu item, we fully qualify the macro name with the the ThisWorkbook.Name so that the proper macro will be executed, regardless of what workbook is active when the menu item is pressed.

The Tag property is simply a string type variable in which you can store anything you like.  One advantage of using this property is that you can assign a "code" to your custom menu items, allowing fast and easy deletion.  

To delete a menu item, you can use code similar to the following: 

Dim CmdBar As CommandBar
Dim CmdBarMenu As CommandBarControl

Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
Set CmdBarMenu = CmdBar.Controls("Tools")
CmdBarMenu.Controls("New Item").Delete

If you have created a number of menu items, you can search for them using the Tag  property. The method FindControl  of the CommandBars  object allows you to search by Tag  value. For example, the following code will delete all controls which have a Tag of "_My_Tag_". 

Dim CmdCtrl As Office.CommandBarControl
Set CmdCtrl = Application.CommandBars.FindControl(Tag:="_My_Tag_")
While Not CmdCtrl Is Nothing
    CmdCtrl.Delete
    Set CmdCtrl = Application.CommandBars.FindControl _
        (Tag:="_My_Tag_")
Wend

In Excel2000, the FindControls method was added to return a  CommandBarControls collection of objects, all of which meet the search criteria. Using this, you can use a  For Each loop to loop through and delete the controls. 

Dim CmdCtrl As Office.CommandBarControl
On Error Resume Next
For Each CmdCtrl In Application.CommandBars.FindControls _
    (Tag:="_My_Tag_")
    CmdCtrl.Delete
Next CmdCtrl

More advanced methods of working with menu items and tool bars with VBA code can be found in the on line help files, the Office VBA Programmers Guide, or most good books about VBA.