CommandBars

The commandbar is a generic term that includes menu bars, toolbars and shortcut menus.
     ● Toolbars - These are either floating or docked.
     ● Menu Bars - Excel has two menu bars Worksheet Menu Bar and Chart Menu Bar.
     ● Shortcut Menus - These are the menus that appear when you use the right mouse button.
Each CommandBar has a Type property that indicates its type

Creating new Menu Items

Excel 5.0 Macros

Workbook.CommandBars

The workbook object also has a CommandBars property but this is only relevant when a workbook is embedded into another application.
In which case this property returns the CommandBars collection for that application.
When applied to a non embedded workbook this property returns Nothing.

Customising your Command Bars

The following is a list of ways you can customise the commandbars in these applications
     ● Remove controls from built-in toolbars. Remove the commands you never use
     ● Add additional controls to the built-in toolbars. These commands can be other built-in commands or custom commands.
     ● Create additional toolbars and menus.
     ● Change the functionality of the built-in commands You can attach your own macro to a built-in control.
     ● Change the image that appears next to the commands.
You can perform these customisations either manually using the (View > Toolbars > Customise) dialog box or by using code.
You can easily reset the built-in commandbars using the Customise dialog box.

CommandBar Properties

CommandBar Methods

Application.CommandBars Collection

Although this is a collection it actually starts at 1 and not 0.
The following line of code will generate an error:

Each CommandBar object contains a collection of Controls.
This collection contains the following:
     ● All the application built-in toolbars
     ● Any other custom toolbars that have been created or loaded
     ● The built-in Menu Bar (or Worksheet Menu Bar in Excel)
     ● The Chart Menu Bar (Excel Only)
     ● Any other custom menus that have been created (??)
     ● All the shortcut menus

CommandBar Controls

In some cases these controls will also have there own collection of controls.

Never refer to the actual captions, non english version may have a different caption.
Never assume a menu item exists

Pop-up menus are displayed in three ways: as menus that drop down from menu bars, as submenus that cascade off menu commands, and as shortcut menus.

Each command bar, other than the main menu, typically contains a collection of controls.

Customising Toolbars & Menus

Is this the same for Excel, Word and PowerPoint ??
It is possible to display your toolbar buttons in 2 different sizes. There is the normal size which most people use and then there is a "large size" which no-one in there right mind would want to use. This can be changed from (View > Toolbars > Customise(Options tab, "Large icons")
The customisation of any menus or toolbars is done using the CommandBars object. This is linked to the (Tools > Customise)(Command Bars tab).

You can easily customise your toolbars. To delete or move a button from a toolbar hold down the Alt and drag the button off the bar to delete or onto another bar. You can also remove buttons while the customise dialog is open (Tools > Customise)
Each toolbar can appear docked at the top, bottom or sides of the application window or as a floating window anywhere in the workspace.
If you want to move or add buttons you must have the Customise dialog box open. Right click a toolbar and select customise. You can also easily add/remove delimiters.
You can protect your toolbars to stop them being modified.
Buttons can be moved and deleted without accessing the Customise dialog box. Just hold down the Alt key and drag, the button either to a different position, to a different toolbar or off the toolbar altogether

You can prevent the user from customising your toolbars by using the protection property. The property can be one or a sum of the following:

Dialog boxes

To find out the name of a dialog box hold down "Alt" and "Ctrl" and press the "+" key on the number keyboard. Activate your dialog box. the name is displayed in the Commands list box. You can preset almost all of the controls in almost any dialog. Set the options in a macro called the same as the dialog box name. To restore just rename or delete the macro.

VBA Tasks

Some tasks can only be carried out using VBA code
     ● Automatically remove or hide a command bar when its related workbook or document is closed
     ● Add a custom command to a built-in menu bar when a workbook is opened and remove it when it is closed
     ● Dynamically change your commands in response to user action
     ● Add combobox controls

To make absolutely sure if a toolbar exists you should loop through all the commandbars and check

you can create a check box on a menu bar

To make a copy of an existing button on a toolbar hold down the Ctrl Key while you select and drag to its desired location.

You can restore built-in menu components but not custom ones. Use the Reset method. To restore the built-in menu components just use the reset method

To disable a menu component (ie grey it out) set the enabled property to false
To copy a button from a toolbar hold down the "Ctrl" + "Alt" down while dragging the button to its new location.

Always have screen tips switched on in your (Tools > Customise > Options) Select "Show screen tips on toolbars" and "show short-cut keys in screen tips" (all but Excel !!).

If any of your default toolbars get customised or buttons get deleted you can reset them from (Tools > Options > Customise) (Toolbars tab). Select the appropriate toolbar and reset it.

If you would like your toolbar to always return to the same place whenever it is visible, you can just create it once and then hide it when you don't need it, instead of creating it and deleting it every time.

Most of the menu commands display a dialog box. In fact you can tell because the command has a ".." suffix to it. If you are creating any custom menus then it is worth keeping to this convention.

You can add a menu to a built-in or custom menu bar. However because Office applications can display different menu bars in different context, you may need to add it to more than one.

When you edit the name of a menu, add an ampersand character "&" before the character you want to be the short-cut.
The Ids of the pop-up controls for built-in menus are in the range 30002 to 30426. Remember that these Ids return empty copies of the built-in menus.
A Pop-Up menu (or short-cut menu) is very similar to a drop-down menu although it is displayed at the pointers current location.
A menu and a menu item are two entirely different things. A menu lists commands that are available. A Menu bar is special area displayed across the top below the title bar. A menu bar will include a set of entries called menu titles.
It is possible to use any of the standard windows icons for your menus and toolbars.

If you create a toolbar with a name that already exists then ???

You can add a checkmark to a button using the state property of the command button.

Deactivating and Activating Menus

Deactivating and Activating Shortcut keys

Let the macros themselves determine which CommandBar button that started them.
If you attach the macro below to multiple CommandBar buttons, the messagebox will display different contents:

Displaying Toolbars

Any commandbar that is not a msoBarTypePopUp which has its Enabled property set to True will appear in the (Tools > Customise) (Toolbar tab) dialog box
You cannot set the Visible to True unless the Enabled is set to True.
You cannot change the Enabled property for the Worksheet Menu Bar
The only way to remove the Worksheet Menu Bar is to set its Enabled property to False.

Disabling Access to the Customise dialog

If you wan to stop users from making changes to the toolbars and menus you can prevent them from accessing the (Tools > Customise) dialog box.

You can also disabled the shortcut menu that appears when you right click on the toolbar bar to display the list of available toolbars.
This corresponds to the (View > Toolbars) menu item.
ToolBar List is a special hidden command bar similar to the Menu Bars command bar controls
The ToolBar List has no index in the CommandBars collection but it can be accessed by using its name.

Sometimes you cant find a toolbar directly. May need to loop through all the toolbars to locate it ??

CommandBar Conventions

• Menu text is always in ProperCase. The first letter of every word should be in capitals except for minor words such as the, a and and

• Each menu must have at least one command as the menu click is not detected

• Every menu item should have a hot key associated with it that is unique to that menu.

• A menu item that displays a dialog box is followed by an ellipsis (..)

• Menu items should be kept relatively short. Submenus should be used to avoid long lists

• Use separator bars to group your commands together

• If possible disable menu items that are not appropriate for the context

objcontrol.Delete

When you delete a commandbarcontrol from a toolbar the BeginGroup is not automatically removed.
The begingroup that was associated with the control is left behind
To ensure that this does not happen always set the BeginGroup to False before deleting any controls.

AutoSensing

Some of the standard toolbars in Microsoft Office applications only appear when you are working in specific item. A good example of this is the Chart toolbar in Excel. This appears automatically whenever a chart is active.
It is possible to build the same functionality to your custom toolbars. This is possible due to the large number of events that can be detected.

Shift Key Detection

Using a Windows API call you can have a button on a toolbar perform a different action if the user presses Shift while the button is pressed.

Creating a new Menu Bar

Creating another menu bar will automatically hide the existing one.
You can have more than one menu bar but only one can be active at any one time.

When this menu bar is deleted the original menu bar will be returned.

Prevent the User from accessing the Toolbar list

This is the shortcut menu that appears when you right click on a command bar.
This displays a list of available toolbars

Excel - ThisWorkbook

When you reference the CommandBar collection from a code module for a ThisWorkbook object you must precede it with a reference to the Application object.

You can get a run-time error when hiding and showing a command bar using the visible property, because this property does not apply to all types of commandbars.

Allows you to select a particular tab

Creating a shortcut menu

Enable all toolbars

Running a macro from a mouse over

Creating a new toolbar

Adding a custom graphic to a button

Disabling a specific shortcut

Disabling all the right click shortcut menus

Adding a button to the standard toolbar ?

Adding a checkmark

Adding a dropdown control

Restoring Defaults

CommandBars.GetImageMso

Creating a new CommandBar

You create a new command bar using the Add method from the CommandBars collection:
This is the same in Excel, Word and PowerPoint.

Name - The name of the new command bar. If omitted a default name is assigned to the command bar (such as Custom 1).
Position - The position or type of the new command bar.
MenuBar - The default value is False. True to replace the active menu bar with the new command bar.
Temporary - The default value is False. True to make the new command bar temporary. Temporary command bars are deleted when the container application is closed.

Preventing a Toolbar from being modified

The Protection constants are additive which means you can apply several to the same commandbar.
The following means the toolbar cannot be customised or moved.

Creating a new CommandBar

Dim objCommandBar As CommandBar

Deleting a CommandBar

Disabling a CommandBar

This effectively removes it from view and also from the Customise dialog box list of toolbars

Quick Cleanup

Adding Command Buttons

This code shows you how to add a command button to an existing Menu.

Removing Command Buttons

This code shows you how to remove a command button from an existing Menu.

CommandBarControl Object

Every control that can be added to a commandbar is a CommandBarControl object.
There are three CommandBarControl objects which you can use in your code:
     ● CommandBarButton - This corresponds to the msoControlButton
In Office 2000 a CommandBarComboBox Change event was added.
These are the most common and should be used for assigning to macros.
     ● CommandBarPopup - This corresponds to the msoControlPopup
This represents a menu or a shortcut menu. This type of control has its own Controls collection and allows controls to be added to it.
     ● CommandBarComboBox - This corresponds to the msoControlComboBox
In Office 2000 a CommandBarComboBox Change event was added.

If you are working with msoControlEdit or msoControlDropDown you should use the generic CommandBarControl object.
A dropdown control has a list property and is an array of its list values and a listindex property.

msoControlTypes

Only five of these command bar controls can be added to your custom toolbars and menus.
msoControlType.msoControlButton - Buttons with or without images
msoControlType.msoControlEdit - Textbox allows you to enter text
msoControlType.msoControlDropDown - Only allows you to select from the list.
msoControlType.msoControlComboBox - Lets you enter a value as well as being able to select from the list.
msoControlType.msoControlPopup - Used for menus and submenus.

CommandBarControl Properties

These properties and methods are shared by all the commadbarcontrols

Parameter

This property can be used to hold any text string that might be useful.
One use might be to use the same Macro for several commands and then examine the Parameter property to determine which button was pressed.
This method is similar to the Application.Caller used in user defined functions.

This can be very useful for toggling

Tag

This property can be used to hold any text string that might be useful and can be used in addition to the Parameter property.

Adding Commands

when adding a built-in control to a command bar, you pass the ID property for the built-in control in the ID argument of the Controls collection's Add method.

Type - The type of control to be added to the specified command bar. Can be one of the following MsoControlType constants.
Id - An integer that specifies a built-in control. If the value of this argument is 1, or if this argument is omitted, a blank custom control of the specified type will be added to the command bar.
Parameter - For built-in controls, this argument is used by the container application to run the command. For custom controls, you can use this argument to send information to Visual Basic procedures, or you can use it to store information about the control (similar to a second Tag property value).
Before - A number that indicates the position of the new control on the command bar. The new control will be inserted before the control at this position. If this argument is omitted, the control is added at the end of the specified command bar.
Temporary - The default value is False. True to make the new control temporary. Temporary controls are automatically deleted when the container application is closed.

Adding Custom Commands

when you create a custom command bar button, you must set both the Style and Caption properties to make sure that a caption will appear on the button.

Assigning Shortcut Keys

The following adds a menu that features a shortcut key.
You must use the MacroOptions to actually create the shortcut key (?)

CommandBars.FindControl

You can also use the FindControl method to return a CommandBarControl object.

This is only available on the CommandBars object.

If no controls are found that fit the criteria then "Nothing" is returned.
If this method find two or more controls that fit the search criteria, the first control that is found is returned.

CommandBars.FindControl(Type, Id, Tag, Visible, Recursive)

The Recursive argument is only available when used on the CommandBar object.

Finding Built-in Commands

Every built-in command (inc menus) has a unique Id associated with it.
This is the best property to use when searching for built-in commands.

Finding Custom Commands

This method can be used to quickly locate controls on toolbars and menus.
This can be really useful when working with your custom toolbars and menus because you can assign a Tag property to all of these commands.
Assuming that all your commands have a unique Tag assigned to them this method will return the corresponding CommandBarControl is returned

Recursive Argument

This argument specifies if you want to recursively search the whole command bar including any drop-down menus and extension controls.
There are no controls passed the third level.
The default argument is False.

Using the Tag property

Moving Commands

moves the control to end

Custom Toolbar Icons

If you are creating custom commandbar controls for Office then you have several options.
The easiest method is just to use the Visual Studio Image Editor in conjunction with the Picture and Mask properties.

Visual Studio Image Editor

The editor provided in Visual Studio has an extensive set of tools for creating and editing images
The editor can work with all the common file formats (png, bmp, jpeg and tiff)

CopyFace and PasteFace

This method will copy the controls image to the clipboard
Some button images are blank and some are missing ??
The blank ones will copy without any problems but you will get an error if you try and copy an image from a control that doesn't has one.

This example finds the first control on the commandbar named "Custom".
If the first control on this custom toolbar is a button, the FindControl method is used to find the Copy button (on the standard toolbar) and then copies the face from this button to the button on the custom toolbar.

Picture and Mask

These two new properties were added to the CommandBarButton in Office 2002.
Picture and Mask can also support gradients in Office 2003 running on Windows XP using themes.

Creating a Transparent Image

Select the Project in the Solution Explorer
Right click and select Properties
Display the Resources tab
Select (Add Resource > New Image > BMP Image)
Enter a meaningful name into the Add New Resource dialog box
The size defaults to (48x48) change this to (16x16)
In this example we are going to use "InsertData"
Pressing Add will display the Visual Studio Image Editor
Create your image (using green (or clear) for your background colour )
Remember that you can copy button images directly from existing toolbars and paste them into your editor using (Edit > Paste)
Once your icon is finished save it
This will be the icon used for Picture property, so now we need to create the corresponding icon for the Mask property.

Select (File > Save Resource As..)
Because our original bitmap is called "InsertData" we are going to save this as "InsertData_mask"
Creating the Mask icon is quite straightforward
Everything you want displayed in the foreground must be in black
Everything you want to be transparent must be in white
Make the necessary changes and save
You then have to add this file to the resources
Select (Add Resource > Add Existing File)
Browse to the Resources folder of your solution and select the file

If you want to remove a resource it is not sufficient to delete it using Solution Explorer. You must click on "Remove Resouce" from the resources tab.

Remember

The Picture & Mask properties cannot be used if the toolbar has been protected.
You must remove any toolbar protection before these properties can be used.

Cell, Row and Column

There are actually 2 of these shortcut menus in Excel
The first is used when the worksheet is in Normal view.
The second is used when the worksheet in in Page Break Preview.

Define a shortcut key

To work with the shortcut menus you need to know the corresponding Caption and Index values
The index values for the shortcut menus have not remained consistent between versions

You should always refer to the Caption for reference

to disable all of the right-click shortcut menus

To disable all of the shortcut menus using the mouse use commandbars("ToolBar List").Enabled = False

It is possible to create an entirely new shortcut menu

Deleting a shortcut menu

Restoring a shortcut menu

Deleting a menu on a shortcut menu

Restoring a menu on a shortcut menu

Deleting a submenu on a shortcut menu

Restores a submenu on a shortcut menu

The Reset method is used to populate the Formulas submenu.

Deleting a command on a submenu

Restoring a command on a submenu

Calling the Same Macro

You might want to include a button that can be used as a toggle.
For example the following button can be used to toggle between sorting ascending and descending.

Passing Parameter Values

For simple VBA macros you can use the Parameter and Tag properties to store information to be passed to the OnAction macro.
If you have more than two items of information to pass it is more convenient to use a macro procedure that has input parameters.
For example if you wanted to pass a product name, cost and selling price to the following macro.

To execute this macro from a commandbar you need to assign the following code to the OnAction property of the control.

The entire expression is enclosed in single quotes.
Any string parameter values within the expression are enclosed in double quotes

ID (Built-in)

All built-in commands have a unique ID associated with them.
This ID is used to find built-in commands on toolbars and menus.

Parameter (Custom)

The Parameter property is the property that uniquely identifies the command.

Tag

The Tag property is used to associate the CommandBarControl with the necessary event handler.
Using the Tag in this way ensures that all the buttons go through the same event handler making it easier to manage.

Caption (Third Party)

This is used to find built-in commands or other commands

Identifying Commands

ButtonTag
ButtonParameter - custom toolbars and menus first
ButtonID -
ButtonCaption - third party and as a last resort
ButtonOnAction -
ControlID -
ControlCaption -
DropDownTag - A tag description is assigned to every custom drop-down menu
DropDownCaption -
ExtensionTag - A tag description is assigned to every custom extension bar
ExtensionCaption -

Application.ListCommands

Creates a new document and then inserts a table of Word commands along with their associated shortcut keys and menu assignments.

Temporary CommandBars

The Temporary parameter of the CommandBars.Add method and the CommandBarControls.Add method does not apply to Word.
The Temporary feature is intended for other applications that do not support session-specific settings.
Because Word can store information in global templates or local templates, you can get all of the Temporary functionality without actually using the Temporary flag.

If you're going to add CommandBar or CommandBarControl objects to Word at run-time, and you want them to be truly temporary, set the Application.CustomizationContext to something other than Normal.dot before you begin making your changes.
You should actually set this to point to your actual template instead.
Make sure you do not set the Temporary to True.

CustomizationContext Property

Returns or sets a Template or Document object that represents the template or document in which changes to menu bars, toolbars, and key bindings are stored.
Use the CustomizationContext property to set the template or document context prior to accessing the CommandBars collection.
Corresponds to the value of the Save in box on the Commands tab in the Customize dialog box (Tools menu).