Page 460 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Microsoft Office Excel 2003 Programming Inside Out
The new command button will fire the ThisWorkbook.RunWizard subroutine when the new
button is clicked. The only thing that the RunWizard routine does is show the wizard’s user
form using one line of code.

Public Sub RunWizard
UserForm1.Show vbModal

End Sub
Notice that the Tag property in the Workbook_Open routine is set to a unique value, to make
it easy to remove the button in the Workbook_BeforeClose event. (See the following listing.)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Chapter 20
Dim c As CommandBar
Dim cb As CommandBarButton
On Error Resume Next
Set c = Application.CommandBars("Worksheet Menu Bar")
If Not c Is Nothing Then
Set cb = c.FindControl(, , "Excel 2k3 WizardApp", , True)
Do While Not cb Is Nothing
cb.Delete
Set cb = c.FindControl(, , "Excel 2k3 WizardApp", , True)
Loop
End If

End Sub
The code for the Workbook_BeforeClose event is probably more complex than is really
needed, but it also ensures that any buttons associated with the wizard application are
deleted. The code merely locates the first control that contains Excel 2k3 Wizard App in the
tag property using the FindControl method. Then the code enters a While loop that will delete
this specific control and then search for the next control with the same Tag value.

Building the UserForm
Because the wizard displays several forms’ worth of information, it’s natural to use the
MultiPage control. The MultiPage control has several properties that make it very useful
for this particular situation. First, the MultiPage control contains a number of individual
Page objects.

For more information about the MultiPage control and the Page objects, see Chapter 19, “Creating
User Forms.”

434