How to create a toolbar for your Excel add-in on installation

If you ever coded an Excel add-in you probably wanted it to automatically create its own toolbar for easy access to its functions.

One of the ways of doing it is to use the auto_open and auto_close functions to create and delete the menus each time you launch and quit Excel, respectively.

This method works fine but it can slow down launching Excel.

A better method is to create the menus when the add-in is installed and remove them when it’s uninstalled, like below:

Private Sub Workbook_AddinInstall()
  createCommandBars "YourToolbar"
End Sub

Private Sub Workbook_AddinUninstall()
  On Error Resume Next
  Application.CommandBars("YourToolbar").Delete
  On Error GoTo 0
End Sub

Private Sub createCommandBars(sn As String)
  Dim cb As CommandBar

  'Create the new toolbar
  Set cb = Application.CommandBars.Add(Name:=sn)

  'And then create any controls
  With cb
    With cb.Controls.Add(Type:=msoControlButton)
      '.BeginGroup = false 'true to put a separator
      .Caption = "Command"
      .OnAction = "YourMacroName"
      .FaceId = 46 'For your command icon
      .TooltipText = "This command does this"
      .Style = msoButtonIcon
    End With
  End With

  'make your toolbar visible
  cb.Visible = True
End Sub

Keep coding!

EDIT: as I came to find out later, Excel requires that the auto_open and auto_close functions be implemented in order to recreate the menus each it is started. So just reuse the functions above 🙂