Updates

2010

Tab Activation and Auto Scaling added - ee691834

Creating new Menu Items

Excel 5.0 Macros

Application.MenuItems.AddMenu 

Working with Command Bars

In Microsoft Office 2003, 2002, 2000 and 1999 applications, there are three kinds of command bars:

• 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

If Application.CommandBars(3).Type = msoBarType.msoBarTypeMenuBar Then 
End If 

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:

Application.CommandBars(0).Name 

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.

CommandBars(2).Controls(2).Caption = "some text" 
CommandBars("Standard").Controls("New").Caption = "some text" 

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).

With CommandBars 
   .DisplayKeysInTooltips = 
   .DisplayToolTips = 
   .LargeButtons = True 
   .MenuAnimationStyle = msoMenuAnimationUnfold 
End With 

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

Does a toolbar exist ?

Sometimes the following line of code will not return a commandbar even though it might exist

Dim objBar As CommandBar 
objBar = Application.CommandBars("My Toolbar") 

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

For icount = 0 to Application.CommandBars.Count - 1 
Next icount 

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

Application.Commandbars("Edit").Controls(3).Enabled = False 
Application.Commandbars("Edit").Controls(3).Enabled = True 

Deactivating and Activating Shortcut keys

Application.OnKey "^c", "" 
Application.OnKey "^c" 

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:

Sub DummyMacro() 
    If Application.CommandBars.ActionControl Is Nothing Then 
    ' the macro was not started from a commandbar button
        MsgBox "This could be your macro running!", vbInformation, _ 
            "This macro was not started from a CommandBar button" 
    Else ' the macro was started from a commandbar button  
        MsgBox "This could be your macro running!", vbInformation, _ 
            "This macro was started from this CommandBar button: " & _ 
            Application.CommandBars.ActionControl.Caption 
    End If 
End Sub 

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.

Application.CommandBars("Tools").Controls("Customize..").Enabled = False 

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.

Application.CommandBars("Toolbar List").Enabled = False 

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.

objcontrol.BeginGroup = False 
objcontrol.Delete 

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.

Set objNewMenu = CommandBars.Add(MenuBar:=True) 

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.

Important

With the exception of Microsoft Excel, the built-in menu bar for each Office application is named "Menu Bar". The Excel main menu is named "Worksheet Menu Bar."