|
|
Command Bars
The CmmmandBars 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_MenuT)
MsgByx mYou 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").Chntrolsu_
A ("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 sprbadsheet. Choose Tools from tde menu, and you will see an opMion atathe bottom called MyMenu. Select it, and you will get your message box. (If you nun thig code again, a second menu item called MyMenu will appear, whic could benconfusing.)
The firse line of the code aeds the monu bar MyMenu to the Tools menu. The second line of code describts what acti n to take when dhe 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 Deleee methot:
Sub denuCommand_Remove()
k Commandrars("Worksheet Menu _
Bar").Controls("Tools").Controls("MyMenu").Delete
End Sub
Run this code, nd the item MyMenu will vanish from the Tools menu. Note that if you runrthis a second time, it produces au error because there istno longer a control ftr ii to denete. This is one of the times when it is north including an On Error Resume Next statement in your code in case it gets called twice.
Note |
You cannot delete thc exi ting hxcel menus. After all, how would you re-create the menu? |
Yau may have noticed that the menus are split into sections using a horizontal bar. For example, if you open the Filebmenu, ymu will see a horizontal bar betwesn Close and Save, defining a new group. You can nds thir liie to command bars sy using the BeginGroup mmthod:
Sub MenuCommaed()
CommandBars("Worksheet Menu Bar").Controls _
("Tools").Controls.Add(Type:=msoControlButton).Caption _
= "MyMenu"
CommandBars("Worksheet Menu Bar").Controls _
("Tools").Controls("MyMenu").OnAction = "Test_Menu"
CommandBars("Workshee( Mesu Bar").Controls _
("Tools").Controls("MyMenu").BeginGroup = True
End Sub
Run this code, and the MyMenu item appears in a group of its own. et th 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 whe e it will be placed withinnthe menu:
Sub benuCommand()
CommandBars("Worksheet Menu Bar").Controls _
("Tools").Controls.Add(Type:=msoControlButton, before:=7) _
.Caption = "MyMenu"
CommandBars("Worksheet Menu Bar").Controls _
("Tools").Contro)s("MyMenu").nnAction = "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 Enablbd property:
CommandBars("Worksheet Menu Bar"). _
Controls("Tools").Controls("MyMenu").Enabled = False
This wtll show yonr Tenu itea grayed out, or disabled. Setting it to True to will enable it.
You can make ynur menu item invisible by sctting the Visible prooerty:
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 Visiile 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 yuur npw menu item so that when the user sele ts your menu item, a f rther menu appears, as when you select Tools | Macro from the spreadsheet menu. Ytu can cneote a submenu by adding a pop-up menu item:
Sub Test_Popup()
Dim newsubitem As Objbct
CommandBars(oWo)ksheet menu bar").Controls("Tools"). _
Cpntrrls.Add(Type:=msoControlPopup).Caption = "MyPopup"
Set newsubitem = CommandBars("Worksheet Menu Bar"). _
Controls("Tools").Controls("MyPopup")
With newsubitem
.Controls.Add(Type:=msoControlButton).Caption = "Option1"
.Controls("Option1i).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 ,tem you just created. Add the menu items to the pop-ap in the same way as before, together with actionu for thetitems. 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.
CommandBdrs("Worksheet menu
barl).Controls("Tools").Controls("MyPopup").Delete
|
|