Working With Commandbars

By using toolbars and shortcut menus, you can provide quick access to commonly used commands. Excel has more than 50 different built-in toolbars and about 60 different shortcut menus, all of which are part of the

CommandBars collection. This collection also contains any new custom toolbars and menus you create. Due to their central location, you can easily make modifications to existing menus and toolbars, or add new ones as needed.


The CommandBars collection is part of the Application object, the main object that contains all Excel-related objects.

Each individual toolbar or menu is a separate CommandBar object. You can reference these objects either by an index value or by the object name. For example, the code CommandBars( "Standard" ) references the Standard toolbar. See Appendix A for a list of the built-in toolbars and shortcut menus in Excel.

Excel determines which type of command bar a particular object contains by the value of the Type property. Excel has three types of command bar objects, as described in the following table.







Menu Bar



ShortCut Menu




You can modify menus and toolbars either manually from Excel or you can create subroutines to modify these CommandBar objects. In Excel, you make modifications to toolbars and menus via the Customize command on the Tools menu. From the Customize menu you can add and remove both menu and toolbar options. See Chapter 1 for more information about adding macro references to toolbars and menus.

You can also write a procedure that modifies a particular menu or toolbar by adding or removing options. Each option on a toolbar or menu is a

CommandBarControl object associated with the corresponding CommandBar object. You reference the

CommandBarControls collection using the

Controls property. You add a new option to a toolbar or menu using the Add method.

When you call the Add method with the Controls object, you can apply the optional parameters of the method to specify the type of control to add. The following table describes each of the parameters you can call with the Add method to create a new control. After you add the control, you reference the CommandBarControl properties to set the specific properties for the individual control. See the sections "Add Controls to a Toolbar" and "Add Items to a Menu" for more information on adding options to toolbars and menus.

+1 0

Post a comment