|
Home > Macros > Basics > Macro Toolbar Excel 2003 Macro ToolbarHow 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.
|
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

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

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
First, you'll save the macro toolbar workbook as an Add-In, and make it accessible from other workbooks, as you work in Excel.
Next, install the Add-In, so you can use the macro toolbar in any workbook.

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 zipped sample file.
Last updated: March 27, 2023 3:50 PM