Creating a Custom Toolbar

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Custom toolbars are not as versatile in Excel 2007 as they are in Excel 2003 and before because they always appear in the Add-InsOCustom Toolbars tab (and they can't be moved). But custom toolbars are still a viable option if you're willing to put up with the limitations. For this reason, I include a simple example of creating a custom toolbar.

The code that follows creates a new toolbar (with two buttons) that is displayed in the Add-ins tab in the Custom Toolbars group of the Ribbon:

Sub CreateToolBar()

Dim cBar As CommandBar

Dim cControl As CommandBarControl


' Create toolbar

Set cBar = Application.CommandBars.Add cBar.Name = "ExcelVBADummies" cBar.Visible = True

' Add a control

Set cControl = cBar.Controls.Add With cControl

.OnAction = "ToolButtonOpen_Click" .TooltipText = "Excel VBA for Dummies Demo: Open" End With

' Add another control

Set cControl = cBar.Controls.Add With cControl

.OnAction = "ToolButtonSave_Click" .TooltipText = "Excel VBA for Dummies Demo: Save" End With End Sub

Notice that I set the FaceID property when I added a control. This property determines the icon on the button. The OnAction property specifies the macro to execute when the button is clicked. The TooltipText property contains text that is displayed when the user hovers the mouse pointer over the control. Figure 19-8 shows the result of running the code:

Figure 19-8:

The Add-ins tab of the Ribbon shows the result of running the CreateTool Bar routine.


rj ;

Boofc2 - Microsoft Exce

y a

Home Insert Page Layout

Formulas Data Review View Developer Add-ins


;om Toolbar





B 1 C 1 D

E 1 F

G H 1

1 K



The custom toolbar is removed with this procedure:



On Error Resume Next


i .Delete



And last but not least, here are the two procedures that respond to the button clicks:







These two procedures execute the appropriate Ribbon commands.

This example is available on the book's Web site. You can also find a workbook that contains a list of all the Excel 2007 Ribbon commands.

When you create a custom toolbar, it remains part of the UI until it is deleted. If the workbook that contains the macros is closed, that workbook is reopened if you click a button on the toolbar.

Part V

Was this article helpful?

0 0


  • Nile
    How to customize a toolbar in excel2007?
    6 months ago

Post a comment