Add-in with VBA

Most add-ins have a common structure

  • A startup module to trap the opening and closing of the add-in.

  • Some code to add our menu items to the commandbars on opening and remove them when closing

  • For the VBE, a class mosule to handle the menu items "Click" events

  • Some code to perform your menu's actions


Change the name of the Project to be prefixed with a "1BET" to they always appear at the top and out of the way.


The VBE uses the same commanbars code as the rest of Office
You normally assign the name of the subroutine to the OnAction property of the CommandBarButton, however in the VBE this is ignored.
Instead there is a click event associated with a CommandBarButton and a Change event associated with a CommandBarComboBox

In order to use these 2 events you must use a class module combined with the WithEvents keyword

Public WithEvents cbButtonEvents As CommandBarButton 
Private Sub cbButtonEvents_Click( ---- )
   Application.Run Ctrl.OnAction
End Sub

The only way to locate specific menu items in a language independent way is to use the unique ID numbers.

CommandBars.Reset - resets everything !!!!
The VBE Project Explorer does not always update correctly when workbooks are added and removed through code.
The easiest way to refresh the display is to hide and then show the main VBE window

Application.VBE.MainWindow.Visible = False 
Application.VBE.MainWindow.Visible = True

The key things to note are:
An object is declared to receive the Click event for the menu items
The Click event is raised by the cbButtonEvents object
The Click event passes the Ctrl object (the menu item or toolbar button) that was clicked.
The code runs the routine specified in the controls OnAction property

Dim moButtonEvents As CBarEvents 

Sub AddMenu()
Dim oAddinBar As CommandBar
Dim oBtn As CommandBarButton

get the add-ins menu
Ser oAddinBar = Application.VBE.CommandBars.FindControl(ID:=30038).CommandBar

'add a button to it
Set oBtn = oAddinBar.Controls.Add(msoControlButton)

set the button's properties
oBtn.Caption = "About My Add-in"
'linking the event handler to the Tag proeprty
'all buttons with the same tag will fire the same click event
oBtn.Tag = "MyAddin"
oBtn.OnAction = "AboutMe"

create an instance of our CBarEvents class
Set moButtonEvents = New CBarEvents
Set moButtonEvents = oBtn
End Sub

Sub AboutMe()
   Call MsgBox("Hello")
End Sub

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext