Adding Menu Items to the VBE

The VBE uses the CommandBar object model rather than the Ribbon, so the procedure for adding menus to the VBE is almost the same as that documented in Chapter 15 for creating popup toolbars. There is one major difference, which is how to run your routine when the menu item is clicked. When adding menu items to Excel's popup toolbars, you set the OnAction property of the CommandBarButton to the name of the procedure to run. In the VBE, the CommandBarButton still has an OnAction property, but it is ignored.

Instead, Microsoft added the Click event to the CommandBarButton (and the Change event to the CommandBarComboBox). To use these events, you have to use a class module containing a variable of the correct type declared WithEvents. So add a class module to the project, give it the name of CMenuHandler, and type in the following code:

Option Explicit

'A variable to hook the click event for all our menus. Private WithEvents mbtnEvents As Office.CommandBarButton

' Subroutine: mbtnEvents_Click

' Purpose: Handle the click event of all our menus, by running the procedure ' stored in the button's OnAction property

Private Sub mbtnEvents_Click(ByVal Ctrl As Office.CommandBarButton, _

CancelDefault As Boolean)

On Error Resume Next 'In case the routine is wrong/doesn't exist

'Run the routine given by the commandbar control's OnAction property Application.Run Ctrl.OnAction

'We handled it OK CancelDefault = True

End Sub The key things to note here are:

□ A variable, mBtnEvents, is declared to receive the Click event for the menu items.

□ The Click event is raised by the object referred to by the mBtnEvents variable (the only one exposed by it).

□ The Click event passes the Ctrl object (the menu item or toolbar button) that was clicked.

□ The code runs the routine specified in the control's OnAction property. The code is simulating the behavior that occurs when adding menu items to Excel's popup toolbars.

To use this procedure, set the mbtnEvents variable to refer to one of your custom menu items. The CommandBars event model is designed in such a way that when setting the reference, you're also creating an association between the mbtnEvents variable and the menu's Tag property. This means that all menu items that share the same Tag property will also raise Click events against that variable. You can now add as many menus as you like, and all their Click events will be handled by that one procedure (as long as you give them all the same Tag property).

Now that you can respond to menus being clicked, all you need to do to build the Add-in is add some custom menus and the procedures to be called from the Click event. The easiest place to create your menus is from within the Class_Initialize event of the CMenuHandler class, which is called when the class is created in the Workbook_Open procedure. You can also include code to tidy up after yourself, by removing the custom menus in the Class_Terminate event. Because you'll be adding lots of menus in this chapter, it makes sense to factor out the menu-creation code into a separate procedure. The entire CMenuHandler class is shown here:

Option Explicit

'A variable to hook the click event for all our menus. Private WithEvents mbtnEvents As Office.CommandBarButton

'A unique tag to identify our menus

Private Const msTAG As String = "Excel2007VBEWorkbookTools" ' Subroutine: Class_Initialize

' Purpose: Called when the class is created in the Workbook_Open event, ' this procedure creates the menus for the Add-In

Private Sub Class_Initialize()

'Just in case some of our menus got left behind, remove any previous remnants DeleteMenus

'We'll add our menus here, later in the chapter

'Associate our event-hook variable with any one of our menus. On Error Resume Next 'In case we don't find any Set mbtnEvents = Application.VBE.CommandBars.FindControl( _ Type:=msoControlButton, Tag:=msTAG)

End Sub

' Subroutine: DeleteMenus

' Purpose: Find all the menus with our unique Tag and delete them

Private Sub DeleteMenus()

Dim oCtl As CommandBarControl On Error Resume Next

For Each oCtl In Application.VBE.CommandBars.FindControls(Tag:=msTAG) oCtl.Delete

Next End Sub

' Subroutine: AddMenu

' Purpose: Add a menu to a menu bar, storing the procedure to run ' in the control's OnAction

Private Sub AddMenu(ByRef oBar As CommandBar, ByVal sCaption As String, _ ByVal sProcedure As String, _ Optional ByVal lPosition As Long, _ Optional ByVal lFacelD As Long, _

Optional ByVal lStyle As MsoButtonStyle = msoButtonCaption, Optional ByVal sTooltip As String)

Dim oBtn As CommandBarButton

'If we were given a position, add it there. If not, add it at the end If lPosition > 0 Then

Set oBtn = oBar.Controls.Add(msoControlButton, , , lPosition, True)

Else

Set oBtn = oBar.Controls.Add(msoControlButton, , , , True) End If

With oBtn

.Tag = msTAG .Caption = sCaption .FaceId = lFaceID .Style = lStyle .TooltipText = sTooltip

.OnAction = "'" & ThisWorkbook.Name & "'!" & sProcedure End With

End Sub

' Subroutine: mbtnEvents_Click

' Purpose: Handle the click event of all our menus, by running the procedure ' stored in the button's OnAction property

Private Sub mbtnEvents_Click(ByVal Ctrl As Office.CommandBarButton, _

CancelDefault As Boolean)

On Error Resume Next 'In case the routine is wrong/doesn't exist

'Run the routine given by the commandbar control's OnAction property Application.Run Ctrl.OnAction

'We handled it OK CancelDefault = True

End Sub

' Subroutine: Class_Terminate

' Purpose: Remove our menus when the class is destroyed,

' i.e. as the workbook is unloaded

Private Sub Class_Terminate()

DeleteMenus End Sub

Throughout the rest of this chapter, you'll be creating menus by adding code in Class_lnitialize to call the AddMenu procedure, passing in the command bar for the menu to be added to and the properties for the menu.

The names for each of the top-level CommandBars in the VBE are shown in the following table. Note that Excel should always recognize these names, regardless of the user's choice of language for the Office User Interface (apart from a few rare exceptions, such as the Dutch menus, in which case you'll get a run-time error). The same is not true for the menu items placed on these toolbars. The only language-independent way to locate specific built-in menu items is to use their ID numbers. A routine to list the ID numbers of built-in menu items is provided in Chapter 15.

Name

Description

Menu Bar

The normal VBE menu bar

Standard

The normal VBE toolbar

Edit

The VBE edit toolbar, containing useful code-editing tools

Debug

The VBE debug toolbar, containing typical debugging tools

UserForm

The VBE UserForm toolbar, containing useful form-editing tools

MSForms

The popup menu for a UserForm (shown when you right-click the UserForm background)

MSForms Control

The popup menu for a normal control on a UserForm

MSForms Control Group

The popup menu that appears when you right-click a group of controls on a UserForm

MSForms MPC

The popup menu for the Multi-Page control

MSForms Palette

The popup menu that appears when you right-click a tool in the Control Toolbox

Table continued on following page

Name

Description

MSForms Toolbox

The popup menu that appears when you right-click one of the tabs at the top of the Control Toolbox

MSForms DragDrop

The popup menu that appears when you use the right mouse button to drag a control between tabs in the Control Toolbox, or onto a UserForm

Code Window

The popup menu for a code window

Code Window (Break)

The popup menu for a code window, when in Break (debug) mode

Watch Window

The popup menu for the Watch window

Immediate Window

The popup menu for the Immediate window

Locals Window

The popup menu for the Locals window

Project Window

The popup menu for the Project Explorer

Project Window (Break)

The popup menu for the Project Explorer, when in Break mode

Object Browser

The popup menu for the Object Browser

Property Browser

The popup menu for the Properties window

Docked Window

The popup menu that appears when you right-click the title bar of a docked window

0 0

Post a comment