Home > Macros > Basics > Macro Toolbar Excel 2003 Macro Toolbar How to create a floating toolbar in Excel 2003 and earlier, that you can open in any workbook, and click on a button, to run that macro. For a floating form with macro buttons for newer versions of Excel, click here.

Introduction

As you work in Excel, you may create several macros, that you'd like to use in a variety of workbooks. The following technique creates a floating toolbar, that you can open in any workbook, and click on a button, to run that macro.

Thanks to Dave Peterson, who wrote this macro toolbar technique

Set up the Macro Toolbar Workbook

First, you'll create a new workbook, to store the code that builds the macro toolbar.

1. Create a new workbook.
2. Press Alt + F11, to open the Visual Basic Editor (VBE), where macros and user defined functions are stored.
3. In the VBE, press Ctrl + R, to open the Project Explorer
4. Find your new workbook in the list. For example, if your workbook is Book3, it will appear as VBAProject (Book3), as shown at right.
5. Right-click on your workbook name in the list, and choose Insert | Module
6. In the code window that appears on the right hand side, paste all the code that's shown below.
7. In the code, replace aaa and bbb with the names of your macros, and add more items to the list, if you have several macros you want on the toolbar.
8. In the code, replace AAA Caption and BBB Caption with the labels that you want to appear on the toolbar.
9. In the code, replace AAA Tip and BBB Tip with the tooltips that you want to appear when you point to a toolbar button.

Add the Macro Toolbar Code

Option Explicit

Public Const ToolBarName _
  As String = "MyToolbarName"
'===========================================
Sub Auto_Open()
  Call CreateMenubar
End Sub

'===========================================
Sub Auto_Close()
  Call RemoveMenubar
End Sub

'===========================================
Sub RemoveMenubar()
  On Error Resume Next
  Application.CommandBars _
    (ToolBarName).Delete
  On Error GoTo 0
End Sub

'===========================================
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNamess As Variant
Dim TipText As Variant

Call RemoveMenubar

MacNames = Array("aaa", _
              "bbb")

CapNamess = Array("AAA Caption", _
              "BBB Caption")

TipText = Array("AAA tip", _
            "BBB tip")

With Application.CommandBars.Add
  .Name = ToolBarName
  .Left = 200
  .Top = 200
  .Protection = msoBarNoProtection
  .Visible = True
  .Position = msoBarFloating

  For iCtr = LBound(MacNames) _
      To UBound(MacNames)
    With .Controls.Add _
      (Type:=msoControlButton)
      .OnAction = "'" _
        & ThisWorkbook.Name _
        & "'!" & MacNames(iCtr)
      .Caption = CapNamess(iCtr)
      .Style = msoButtonIconAndCaption
      .FaceId = 71 + iCtr
      .TooltipText = TipText(iCtr)
    End With
  Next iCtr
End With
End Sub

'===========================================
Sub AAA()
  MsgBox "aaa"
End Sub

'===========================================
Sub BBB()
  MsgBox "bbb"
End Sub

Save the File as an Add-In

First, you'll save the macro toolbar workbook as an Add-In, and make it accessible from other workbooks, as you work in Excel.

1. In the VBE, choose File | Close and Return to Microsoft Excel.
2. From the Excel menu bar, choose File | Save As.
3. Type a name for the file, e.g. MyMacrosTB
4. From the Save as type dropdown, choose Microsoft Office Excel Add-In (*.xla), at the bottom of the list.
5. Your AddIns directory will automatically be selected, e.g. C:\Documents and Settings\Contextures\Application Data\Microsoft\AddIns.
6. Click the Save button, to save the file.
7. Close Excel.

Install and Use the Add-In

Next, install the Add-In, so you can use the macro toolbar in any workbook.

1. Open Excel.
2. From the Excel menu bar, choose Tools | Add-Ins.
3. Find your Add-In in the list, and add a check mark to its name.
4. Click OK to close the Add-Ins dialog box.
5. The MyToolbarName toolbar should appear, floating over the worksheet. You can drag it to another position, if you prefer.
6. Click on a button to run that macro.

Note: If you're new to macros, you may want to read David McRitchie's intro at: https://www.mvps.org/dmcritchie/excel/getstarted.htm   

Download the Sample File

Download the zipped sample file.

More Tutorials

FAQs, Excel VBA, Excel Macros  

Create an Excel UserForm

UserForm with ComboBoxes

Edit Your Recorded Macro

Excel VBA Getting Started  

MS Developer Resources