Microsoft Office Tutorials and References
In Depth Information
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 Ta g property in the Workbook_Open routine is set to a unique value, to make
it easy to remove the button in the Wo rkbook_BeforeClose event. (See the following listing.)
Private Sub Workbook_BeforeClose(Cancel As Boolean)
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 Ta g 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
Search JabSto ::




Custom Search