Search Contextures Sites

 

Excel -- Macro Toolbar

Set up the Workbook  
Add the Toolbar Code  
Save the File as an Add-In  
Install and Use the Add-In  

 
Thanks to Dave Peterson, who wrote this 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.


Set up the Workbook

First, you'll create a new workbook, to store the code that builds the 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 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 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 it 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: http://www.mvps.org/dmcritchie/excel/getstarted.htm   

 

 
       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store
Last updated: September 6, 2008 10:16 PM