Microsoft Office Tutorials and References
In Depth Information
Part 5: Manipulating Excel Objects
Creating Advanced User Forms
Step 3 of the wizard lets the user review all of her choices before clicking Finish.
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"