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

'http://www.cpearson.com/excel/creatingcomaddin.aspx

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

HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.0\Addins\ 

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.

Application.VBE.CommandBars
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