How to programmatically add a toolbar button (and OnClick handler) to Excel

Question

How do I programmatically add a toolbar (with buttons on it) to Excel (2002 or later)?

When the button is clicked I want a handler to create my COM object and call a method on it?

Answer 1

You could write an Excel add-in that creates a toolbar with your button and COM-calling code, then drop the .xla file you create in the user's XLStart folder.

Answer 2

This is the basis for something that should work on versions up to but not including Excel 2007, which has a completely different interface.

This goes in your ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteCommandBar
End Sub
Private Sub Workbook_Open()
    ShowToolbar
End Sub

And this can go in the same module or a separate one, your choice, although I prefer to put it in its own module where it can be more visible. YOu shouldn't need an OnClick, the button is told what routine to call when you create the button.

Private Const TOOLBARNAME = "MyFunkyNewToolbar"

Public Sub ShowToolbar()
' Assumes toolbar not already loaded '
    Application.CommandBars.Add TOOLBARNAME
    AddButton "Button caption", "This is a tooltip", 526, "NameOfASubInYourVBACode"
    ' call AddButton more times for more buttons '
    With Application.CommandBars(TOOLBARNAME)
        .Visible = True
        .Position = msoBarTop
    End With
End Sub

Private Sub AddButton(caption As String, tooltip As String, faceId as Long, methodName As String)
Dim Btn As CommandBarButton
    Set Btn = Application.CommandBars(TOOLBARNAME).Controls.Add
    With Btn
        .Style = msoButtonIcon
        .FaceId = faceId ' choose from a world of possible images in Excel: see http://www.ozgrid.com/forum/showthread.php?t=39992 '
        .OnAction = methodName
        .TooltipText = tooltip
    End With        
End Sub

Public Sub DeleteCommandBar()
    Application.CommandBars(TOOLBARNAME).Delete
End Sub