Home > Excel VBA macros > Your own toolbar

Make your own toolbar in Excel with VBA macros

It is easy to make your own toolbars in Excel with a few macros written in VBA. You control the look (e.g. what icons to use for buttons) and assign a macro or a built-in Excel function to each button.

Toolbar

You pick the icons from the many FaceIds available in Microsoft Office. At this page is an overview with corresponding numbers.

To test the example, highlight the code with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module. Run the macro "CreateMyTool".

You can make the toolbar active in one spreadsheet only. In that case copy the last 2 macros (Private Sub Workbook_Activate og Private Sub Workbook_Deactivate) into the code sheet for ThisWorkbook. Then the toolbar is activated and deactivated, when the workbook is activated or deactivated.

Option Explicit

Sub CreateMyTool()
'Makes a toolbar called "cbMyTool"

Dim cbMyTool As CommandBar
Dim cbbMyButton As CommandBarButton

'Make the toolbar
Set cbMyTool = CommandBars.Add

'Now we add a button to the toolbar. FaceId is the button's icon,
'OnAction is the macro to run, if the button is clicked, and
'ToolTipText is the text that will show when the mouse hovers.
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 645
   .TooltipText = "Do magic with numbers"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro2"
   .FaceId = 940
   .TooltipText = "Show a message box"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro3"
   .FaceId = 385
   .TooltipText = "Functions"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 1662
   .TooltipText = "Constraints"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro2"
   .FaceId = 225
   .TooltipText = "Lock cells"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro3"
   .FaceId = 154
   .TooltipText = "Delete all"
End With

'Another button is added
Set cbbMyButton = cbMyTool.Controls.Add(msoControlButton)
With cbbMyButton
   .OnAction = "DummyMacro1"
   .FaceId = 155
   .TooltipText = "Return"
End With

'Before we finish, the toolbar gets a name, width and
'is put on the screen.
With cbMyTool
   .Name = "Shortcuts"
   .Left = Application.ActiveWindow.Width
   .Top = Application.ActiveWindow.Height
   .Visible = True
   .Width = 300
End With

BeforeExit:
Set cbMyTool = Nothing
Set cbbMyButton = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description & " CreateMyTool", vbOKOnly + vbCritical, "Error"
Resume BeforeExit
End Sub

Sub DeleteMyTool()
'Removes the toolbar "Shortcuts".
'If it doesn't exist we get an error,
'and that is why we use On Error Resume Next.

On Error Resume Next

CommandBars(Shortcuts).Delete

On Error GoTo 0

End Sub

Sub RemoveToolBar()
'Removes all not-built-in menus and
'toolbars and restores Excel's standard
'menu. This is an alternative to
'DeleteMyTool, that only removes
'the named toolbar Shortcuts.

Dim cbBar As CommandBar

On Error GoTo ErrorHandle

For Each cbBar In Application.CommandBars
    If Not cbBar.BuiltIn Then cbBar.Delete
Next

Exit Sub
ErrorHandle:
MsgBox Err.Description & " RemoveMenu", vbOKOnly, "Error"
End Sub


'Here are the macros that will run
'when the toolbar's buttons are clicked.

Sub DummyMacro1()
    MsgBox "Write a macro instead of this message.", vbOKOnly, "Hello"
End Sub

Sub DummyMacro2()
    MsgBox "Write some VBA code.", vbOKOnly, "You are under arrest!"
End Sub

Sub DummyMacro3()
    MsgBox "Money makes the world go around.", vbOKOnly, "Pay now!"
End Sub

Copy the following two macros to the code sheet of "ThisWorkbook". In the VBA editor you double click on the icon ThisWorkbook just above the module overview in Project Explorer. If it isn't visible, press CTRL+R.

The macros will make your toolbar visible only when the workbook is active. If you want the toolbar to be visible on a certain worksheet, pick the code sheet for that particular worksheet instead of ThisWorkBook.

Private Sub Workbook_Activate()
CreateMyTool
End Sub

Private Sub Workbook_Deactivate()
DeleteMyTool
End Sub

As mentioned earlier there are thousands of small icons (FaceIds). You can see them and the corresponding numbers at this page.

Related: