Page 459 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Creating Advanced User Forms

Chapter 20

Figure 20-7. Step 3 of the wizard lets the user review all of her choices before clicking Finish.

Handling Menus

The first step in building the wizard application is to trap the workbook’s Open event to add
the necessary menu button. This also means that the BeforeClose event should also remove
the menu button.
In the Workbook_Open event in the ThisWorkbook module (see the following listing), a com€
mand bar button is added to the Tools menu. The first step is to locate the Worksheet Menu
Bar through the Application.CommandBars collection. Once the proper command bar is
located, then the specific popup control for the Tools menu is located. Finally, a new com€
mand button is added to the end of the popup control.

Private Sub Workbook_Open()
Dim c As CommandBar
Dim cb As CommandBarButton
Dim cp As CommandBarPopup

On Error Resume Next
Set c = Application.CommandBars("Worksheet Menu Bar”)
If Not c Is Nothing Then
Set cp = c.Controls("&Tools”)

If Not cp Is Nothing Then
Set cb = cp.Controls.Add(msoControlButton)
cb.Tag = "Excel 2k3 WizardApp"
cb.Style = msoButtonCaption
cb.Caption = "Excel 2k3 Wizard"
cb.OnAction = "ThisWorkbook.RunWizard"
End If

End If

End Sub

433