Creating an Old-Style Toolbar

If you find that customizing the Ribbon is just too much work, you may be content to create a simple custom toolbar using the pre “Excel 2007 CommandBar object. This technique is perfectly suitable for any workbook that only you will be using. It's an easy way to provide quick access to a number of macros.

In this section, I provide boilerplate code that you can adapt as needed. I don't offer much in the way of explanation. For more information about CommandBar objects, search the Web or consult the previous edition of this book. CommandBar objects can be much more powerful than the example presented here.

Limitations of old-style toolbars in Excel 2007

If you decide to create a toolbar for Excel 2007, be aware of the following limitations:

• It cannot be free-floating.

• It will always appear in the Add-Ins Custom Toolbars group (along with any other toolbars).

• Some of the CommandBar properties and methods are simply ignored by Excel.

Code to create a toolbar

The code in this section assumes that you have a workbook with two macros (named Macro1 and Macro2 ). It also assumes that you want the toolbar to be created when the workbook is opened and deleted when the workbook is closed.

Note  

Unlike Ribbon modifications, custom toolbars are visible regardless of which workbook is active.

In the ThisWorkbook code module, enter the following procedures. The first one calls the procedure that creates the toolbar when the workbook is opened. The second calls the procedure to delete the toolbar when the workbook is closed:

 Private Sub Workbook_Open()     Call CreateToolbar End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean)     Call DeleteToolbar End Sub 

CROSS-REFERENCE  

In Chapter 19, I describe a potentially serious problem with the Workbook_BeforeClose event. Excel's "Do you want to save " prompt displays after the Workbook_BeforeClose event handler runs. So if the user clicks Cancel , the workbook remains open , but the custom menu items have already been deleted. In Chapter 19, I also present a way to get around this problem.

The CreateToolbar procedure follows :

 Const TOOLBARNAME As String = "MyToolbar" Sub CreateToolbar()     Dim TBar As CommandBar     Dim Btn As CommandBarButton '   Delete existing toolbar (if it exists)     On Error Resume Next     CommandBars(TOOLBARNAME).Delete     On Error GoTo 0 '   Create toolbar     Set TBar = CommandBars.Add     With TBar     .Name = TOOLBARNAME     .Visible = True End With '   Add a button     Set Btn = TBar.Controls.Add(Type:=msoControlButton) With Btn     .FaceId = 300     .OnAction = "Macro1"     .Caption = "Macro1 Tooltip goes here" End With '   Add another button     Set Btn = TBar.Controls.Add(Type:=msoControlButton)     With Btn         .FaceId = 25         .OnAction = "Macro2"         .Caption = "Macro2 Tooltip goes here"     End With End Sub 

CD-ROM  

A workbook that contains this code is available on the companion CD-ROM. The filename is  old-style toolbar.xlsm .

Figure 22-16 shows the two-button toolbar.

Figure 22-16: An old-style toolbar, located in the Custom Toolbars group of the Add-Ins tab.

I use a module-level constant, TOOLBAR , which stores the toolbar's name. This name is also used in the DeleteToolbar procedure, so using a constant ensures that both procedures work with the same name.

The procedure starts by deleting the existing toolbar that has the same name (if such a toolbar exists). Including this statement is useful during development and also eliminates the error you get if you attempt to create a toolbar using a duplicate name.

The toolbar is created by using the Add method of the CommandBars object. The two buttons are added by using the Add method of the Controls object. Each button has three properties:

• FaceID : A number that determines the image displayed on the button.

• OnAction : The macro that is executed when the button is clicked.

• Caption : The screen tip that appears when you hover the mouse pointer over the button.

Tip  

Rather than set the FaceID property, you can set the Picture property using any of the imageMso images. For example, the statement below displays a green check mark:

 .Picture = Application.CommandBars.GetImageMso _         ("AcceptInvitation", 16, 16) 

For more information about imageMso images, see the sidebar, "Using imageMso Images."

When the workbook is closed, the Workbook_BeforeClose event procedure fires, which calls DeleteToolbar :

 Sub DeleteToolbar()     On Error Resume Next     CommandBars(TOOLBARNAME).Delete     On Error GoTo 0 End Sub