Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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.

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

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

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.

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