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
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext