Excel VBA Macro Programming

The CommandBars object represents the Excel spreadsheet menus and allows you to add your own menu commands into the Excel standard commands. For example, you can make it so that when you select Tools from the spreadsheet menu, there's a menu item called MyCode showing in the options. This adds a very professional look to your application, and provided you include the means to remove the entry from the command bar, the user will be impressed that your custom menu item forms part of the standard Excel menu system.

Here is a simple example that adds a new menu item under Tools and attaches some code to it. Insert a module and then add the following subroutine:

Sub Test_Menu() MsgBox "You pressed my menu item" End Sub

When this is run, it will display a message box with the message ‚“You pressed my menu item. ‚½

Now add the following code. Note that there is a continuation character ( underscore ) shown in two of the lines. This allows long lines of code to wrap around onto the next line but still execute:

Sub MenuCommand() CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls.Add _ (Type:=msoControlButton).Caption = "MyMenu" CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls("MyMenu").OnAction = "Test_Menu" End Sub

Run the code only once and then go to the spreadsheet. Choose Tools from the menu, and you will see an option at the bottom called MyMenu. Select it, and you will get your message box. (If you run this code again, a second menu item called MyMenu will appear, which could be confusing.)

The first line of the code adds the menu bar MyMenu to the Tools menu. The second line of code describes what action to take when the user does this. The OnAction property is set to point to the subroutine Test_Menu , which you just created.

If you exit Excel and load the application again, even without your file present, your menu item will still be there. It appears to be permanent, so how can you remove the menu entry? By using this Delete method:

Sub MenuCommand_Remove() CommandBars("Worksheet Menu _ Bar").Controls("Tools").Controls("MyMenu").Delete End Sub

Run this code, and the item MyMenu will vanish from the Tools menu. Note that if you run this a second time, it produces an error because there is no longer a control for it to delete. This is one of the times when it is worth including an On Error Resume Next statement in your code in case it gets called twice.

You may have noticed that the menus are split into sections using a horizontal bar. For example, if you open the File menu, you will see a horizontal bar between Close and Save, defining a new group . You can add this line to command bars by using the BeginGroup method:

Sub MenuCommand() CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls.Add(Type:=msoControlButton).Caption _ = "MyMenu" CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls("MyMenu").OnAction = "Test_Menu" CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls("MyMenu").BeginGroup = True End Sub

Run this code, and the MyMenu item appears in a group of its own. Set the BeginGroup property to True to draw a line across the menu list, defining a group of menu items.

You can also specify where on the menu list you want the item to appear. The default is to always appear at the bottom, but the before parameter allows you to specify where it will be placed within the menu:

Sub MenuCommand() CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls.Add(Type:=msoControlButton, before:=7) _ .Caption = "MyMenu" CommandBars("Worksheet Menu Bar").Controls _ ("Tools").Controls("MyMenu").OnAction = "Test_Menu" End Sub

The before parameter is set to 7, which makes it the seventh item. Subsequent items in the menu bar will be moved down so that the existing menu item 7 will not be lost ‚ it now becomes menu item 8.

You can also enable and disable your menu items by setting the Enabled property:

CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("MyMenu").Enabled = False

This will show your menu item grayed out, or disabled. Setting it to True to will enable it.

You can make your menu item invisible by setting the Visible property:

CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("MyMenu").Visible = False

Your menu item will no longer be in the list, but it can be made visible again by setting the Visible property to True.

The Enabled and Visible properties can be set for all the existing menu items in Excel. Although you cannot delete these, you can hide them and replace them with your own custom menu structure. However, a great deal of caution should be exercised when doing this to make sure none of the built-in functionality is lost. The Reset method can be used to reset a menu back to default menu structure:

CommandBars("Worksheet Menu Bar").Controls("Tools").Reset

You also may want to add a submenu onto your new menu item so that when the user selects your menu item, a further menu appears, as when you select Tools Macro from the spreadsheet menu. You can create a submenu by adding a pop-up menu item:

Sub Test_Popup() Dim newsubitem As Object CommandBars("Worksheet menu bar").Controls("Tools"). _ Controls.Add(Type:=msoControlPopup).Caption = "MyPopup" Set newsubitem = CommandBars("Worksheet Menu Bar"). _ Controls("Tools").Controls("MyPopup") With newsubitem .Controls.Add(Type:=msoControlButton).Caption = "Option1" .Controls("Option1").OnAction = "Test_Menu" .Controls.Add(Type:=msoControlButton).Caption = "Option2" .Controls("Option2").OnAction = "Test_Menu" End With End Sub

In the first line, an object called newsubitem is created to hold the pop-up menu object. This is not strictly necessary, but it makes it easier to code and to follow.

The menu item MyPopup is then created in the same way as a standard menu item, but this time you set the type to msoControlPopup .

The object newsubitem is set to that pop-up menu item you just created. Add the menu items to the pop-up in the same way as before, together with actions for the items. Your menu should now look like Figure 11-1.

Figure 11-1: An additional menu structure created on Excel

You remove the pop-up in the same way as before.

CommandBars("Worksheet menu bar").Controls("Tools").Controls("MyPopup").Delete