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.
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: April 21, 2021 3:45 PM