Last updated: January 27, 2011 1:34 PM
Search Contextures Sites
Thanks to Dave Peterson, who wrote this macro toolbar technique.
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.
Download the zipped sample file.
First, you'll create a new workbook, to store the code that builds the macro toolbar.
- Create a new workbook.
- Press Alt + F11, to open the Visual Basic Editor (VBE), where macros and user defined functions are stored.
- In the VBE, press Ctrl + R, to open the Project Explorer
- Find your new workbook in the list. For example, if your workbook is Book3, it will appear as VBAProject (Book3), as shown at right.
- Right-click on your workbook name in the list, and choose Insert | Module
- In the code window that appears on the right hand side, paste all the code that's shown below.
- 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.
- In the code, replace AAA Caption and BBB Caption with the labels that you want to appear on the toolbar.
- In the code, replace AAA Tip and BBB Tip with the tooltips that you want to appear when you point to a toolbar button.
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.
- In the VBE, choose File | Close and Return to Microsoft Excel.
- From the Excel menu bar, choose File | Save As.
- Type a name for the file, e.g. MyMacrosTB
- From the Save as type dropdown, choose Microsoft Office Excel Add-In (*.xla), at the bottom of the list.
- Your AddIns directory will automatically be selected, e.g. C:\Documents and Settings\Contextures\Application Data\Microsoft\AddIns.
- Click the Save button, to save the file.
- Close Excel.
Next, install the Add-In, so you can use the macro toolbar in any workbook.
- Open Excel.
- From the Excel menu bar, choose Tools | Add-Ins.
- Find your Add-In in the list, and add a check mark to its name.
- Click OK to close the Add-Ins dialog box.
- The MyToolbarName toolbar should appear, floating over the worksheet. You can drag it to another position, if you prefer.
- 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: http://www.mvps.org/dmcritchie/excel/getstarted.htm
Contextures Inc., Copyright ©2012
All rights reserved.