Creating an addin from an Excel macro

Pages in this article

Toolbars

A toolbar can be a very convenient way to give users access to your macros.

Creating a toolbar and adding a button to it is easy enough, see listing 3.

Listing 3

Option Explicit

Sub RemoveBar()
    On Error Resume Next
    Application.CommandBars("xlUtilDemo1").Delete
End Sub

Sub CreateBar()
    Dim oBar As CommandBar
    Dim oControl As CommandBarControl
    RemoveBar
    Set oBar = Application.CommandBars.Add
    oBar.Name = "xlUtilDemo1"
    oBar.Visible = True
    Set oControl = oBar.Controls.Add(ID:=1, Before:=1)
    oControl.OnAction = "ButtonClicked"
    oControl.FaceId = 275
    oControl.Caption = "Click me!"
    Set oControl = Nothing
    Set oBar = Nothing
End Sub

Sub ButtonClicked()
    MsgBox "Your button works!!!"
End Sub

The Sub Createbar creates one custom toolbar with one button, attached to the sub "ButtonClicked".

Note that this code creates a toolbar whos buttons will appear in the add-ins tab of Excel's ribbon.

To make sure the toolbar is created at workbook open and destroyed at closure, the thisworkbook module should contain the following code:

Listing 4

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveBar
    RemoveMenu
End Sub

Private Sub Workbook_Open()
    CreateBar
    MakeMenu
End Sub

There is a disadvantage to this method. When the user decides to cancel a shutdown of Excel (or closing the workbook), the Workbook_BeforeClose event code has already run, so the toolbar is destroyed, even though Excel hasn't been closed. To make sure the toolbar returns after such a cancellation, the code needs some modification.

1. Add a public variable to a normal module:

Public bMeClosing As Boolean

And change the code in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    RemoveBar
    RemoveMenu
    If Not bMeClosing Then
        Application.OnTime Now, "Createbar"
        Application.OnTime Now, "MakeMenu"
    End If
End Sub

So how does this work?
When the user closes Excel himself, the variable "bMeClosing" will be False and an OnTime event is scheduled which will run the CreateBar subroutine immediately after Excel has finished handling all events it needs to handle during its closing routine, including saving unsaved workbooks, etcetera.

If the user decides to cancel the closing operation, then the OnTime event will fire and the toolbar gets rebuilt. If however the user does not cancel the closure, nothing happens, because Excel has shut down.

A special situation arises when only the workbook with the code is to be closed (e.g. when an add-in is uninstalled). Then the Workbook_BeforeClose code runs, sets the OnTime event and the workbook closes. But immediately Excel will load the workbook again to process the scheduled OnTime event. It will even show the enable macros dialog again.

To shut down the utility itself, one needs to change the value of bMeClosing to true and subsequently close. In any normal module:

Sub CloseMeNow()
bMeClosing = True
ThisWorkbook.Close
End Sub

Excel 2007 and up

Toolbars have been stripped from Excel 2007 and up. Any custom toolbars you create will appear as a separate group on the add-ins tab. If you don't like this, the only alternative you have is to do ribbon customisation (see previous page) and add your controls there.

Frequently asked Questions

How can I create a custom toolbar in Excel using VBA?

What is the purpose of the CreateBar subroutine in the VBA code?

How do I ensure a custom toolbar is created when the workbook opens and removed when it closes?

What issue arises when a user cancels the shutdown of Excel regarding the toolbar?

How can I modify the VBA code to handle the cancellation of Excel closing and restore the toolbar?

What is the role of the public variable bMeClosing in the VBA code?

How does the Workbook_BeforeClose event use Application.OnTime to manage toolbar creation?

What special situation occurs when only the workbook with the add-in code is closed?

How can I properly shut down the utility and prevent the toolbar from being recreated?

How are custom toolbars handled in Excel 2007 and later versions?