Adding The Menu Item

The AddMenuItem procedure shown in Listing 23-5 is executed when the workbook is opened. It creates a new GridLines menu item on the View menu.

Listing 23-5: Augmenting a Built-in Excel Menu

Dim AppObject As New XLHandler

Sub AddMenuItem()

Dim ViewMenu As CommandBarPopup Dim NewMenultem As CommandBarButton

' Delete the menu if it already exists Call DeleteMenultem

' Find the View Menu

Set ViewMenu = CommandBars(1).FindControl(ID:=30004) If ViewMenu Is Nothing Then

MsgBox "Cannot add menu item." Exit Sub

Else

Set NewMenuItem = ViewMenu.Controls.Add _

(Type:=msoControlButton) With NewMenuItem

.Caption = "&GridLines" .OnAction = "ToggleGridlines" End With End If

' Set up application event handler

Set AppObject.AppEvents = Application End Sub

The AddMenuItem procedure adds the new menu item to the Worksheet Menu Bar and not the Chart Menu Bar. Therefore, the new menu item isn't displayed when a chart sheet is active (which is just what you want).

Notice that the final statement in the AddMenuItem procedure sets up the application-level events that will be monitored. These event procedures, which are stored in a class module named XLHandler, are as follows:

Public WithEvents AppEvents As Excel.Application

Private Sub AppEvents_SheetActivate(ByVal Sh As Object)

Call CheckGridlines End Sub

Private Sub AppEvents_WorkbookActivate _ (ByVal Wb As Excel.Workbook) Call CheckGridlines End Sub

Private Sub AppEvents_WindowActivate _

(ByVal Wb As Workbook, ByVal Wn As Window) Call CheckGridlines End Sub

This procedure has one flaw: Changing the gridline setting from the Options dialog box is not detected.

0 0

Post a comment