Leading the way in Microsoft Office Development

Defining Groups on your Toolbars and Menus

Grouping commands can be useful and is easily done using the shortcut menu.

When the (Tools > Customise) dialog box is displayed you can select a command with the Right mouse button to display a Shortcut menu.

The "Begin a Group" button is a toggle and will insert a small horizontal or vertical line above or to the left of this command.

You can alternatively use the "Modify Selection" drop-down menu on the Command tab of the Customise dialog box.

Adding and removing groups can also be done using the mouse although this is a little tricky.

To add a group line above a command just drag the command down (or to the left slightly).

To remove a group line just drag the command up (or to the right slightly).

Displaying Text Only or Text and an Image

The following buttons on the Shortcut menu all control how your commands appear although there are subtle differences.

Default Style - This restores the default setting which is normally to display the image and text on menus but just the image on toolbars.

Text Only (Always) - The image will not be displayed when on a menu or on a toolbar.

Text Only (in Menus) - The image will not be displayed when on a menu, but will be displayed when on a toolbar.

Image and Text - The text and the image will be displayed when on a menu or on a toolbar.

(View > Full Screen)

You can quickly remove all the toolbars except the Menu bar and display your worksheet on the full screen by pressing (View > Full Screen).

This will display a larger worksheet area which can often make it easier to work.

The Full Screen toolbar will automatically be displayed which allows you to close the full screen view.

You can alternatively select (View > Close Full Screen) to display all the toolbars.

Using Custom Toolbars

If you open a workbook that contains a custom toolbar (that has been attached to the workbook) the toolbar will stay behind when the workbook is closed.

The reason for this is because the toolbar has now been saved to the (".xlb") file meaning it will always appear when Excel is open.

When this toolbar is saved to the (".xlb") file it also stores a reference to the file that contains the associated event procedures.

This means that when you click a button on the toolbar Excel will automatically load the corresponding file and execute the code.

This means that every toolbar button is functional whether or not the associated file is actually loaded.

Problems can arise though when the location of the file is changed. Clicking on a toolbar button in this case will display an error message.

Even if you know where the file is located loading it manually will not resolve the error either.

If there is more than one toolbar with the same name, the copy that is part of the default Excel workspace (i.e. saved in the ".xlb" file) will be the only one displayed.

The only solution to this mess is to close all the workbooks and delete the offending toolbar using (Tools > Customise).

Open Excel and then load the file manually before using the toolbar.

Saving your settings (".xlb" )

Any customisation that you do to your toolbars and menus is stored in an ".xlb" file on the C drive.

The file contains information on the position and visibility of all toolbars and menus including any custom toolbars and menus.

To totally reset all your toolbars and menus and to discard any personalisation exit Excel and delete the Excel.xlb file. A new one will be created the next time you open Excel.

Excel 2003 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Excel\Excel11.xlb.

Excel 2002 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Excel\Excel10.xlb.

Excel 2000 - C:\Documents and Settings\"user name"\Application Data\Microsoft\Excel\Excel9.xlb.

Excel 97 - C:\Windows\username8.xlb.

This ".xlb" file is re-saved every time Excel is closed.

This file is user specific so two individuals using the same computer will have different ".xlb" files.

The larger this file is the longer Excel will take to open and close.