Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
The Pages Collection with a Numeric index.
An example of this would be:
MsgBox MultiPage1.Pages(0).Caption
It is important to note here that the index 0 always refers to the first page of a MultiPage Control. The second page would be Pages(1) etc.
Using the Pages Collection with the Item Method.
MsgBox MultiPage1.Pages.Item(1).Caption
It is important to note here that the Item Index 0 always refers to the first page of a MultiPage Control. The second page would be Pages.Item(1) etc.
Yet another method we could use is the Page Name Property of a MultiPage
MsgBox MultiPage1.Pages("Page2").Caption
It is important to note that the Page Name Property is not necessarily the Caption of the particular MultiPage we want. For example, we changed the Caption Property of Page2 to Test. This means that we would still refer to this page via VBA code by using the example shown above. In a nutshell this simply means you should not confuse the Caption Property with the Name Property.
One other method we can use is by using the Page Object itself and thus bypassing the Pages Collection.
MsgBox MultiPage1.Page1.Caption
Obviously, in the above example, we must have a page on our MultiPage Control with its Page Name Property set to Page1.
The final method that can be used is by using the SelectedItem Property. This method, when used in a similar way to the above examples, would always return the name of the active page of the MultiPage at the time of the code being run.
MsgBox MultiPage1.SelectedItem.Caption
Value Property
While the Pages Collection allows us to refer to any particular page of a MultiPage, it cannot be used to physically activate or select a particular page of our MultiPage. To be able to do this, we need to use the Value Property of the MultiPage. For example, if we wanted our UserForm to always default to Page1 of our MultiPage we would use
Private Sub UserForm_Initialize ()
Multipage1.Value = 0
End Sub
By placing code like this in the Initialize Event of the UserForm, our MultiPage will always present the User with the first page. Note also that we used 0 and not 1 to activate the first page. This is because 0 refers to Page1, 1 refers to Page2 etc.
There is not much else that the Value Property can be used for, other than as shown in the above example. In other words, we can only parse a whole number value to it or have the Value Property parsed back to us. By this I mean if we needed to check which page of the UserForm was active before running some code, we could simply use
Private Sub UserForm_Initialize ()
If Multipage1.Value = 0 Then
MsgBox "Page1 is active"
End If
End Sub
Ok, to ensure we have the same controls and control names
For Textbox2 place in this code:
For the Next button use this code:
Private Sub CommandButton2_Click()
'Advance by one page
iPageNo = MultiPage1.Value + 1
MultiPage1.Pages(iPageNo).Visible = True
MultiPage1.Value = iPageNo
End Sub
For the Back button use this code:
Private Sub CommandButton1_Click()
'Go back by one page
iPageNo = MultiPage1.Value - 1
MultiPage1.Pages(iPageNo).Visible = True
MultiPage1.Value = iPageNo
End Sub
For the Multipage1 use this code:
Private Sub MultiPage1_Change()
Select Case MultiPage1.Value
Case 0
Label4.Caption = "Page 1 of 3"
CommandButton1.Enabled = False
MultiPage1.Pages(iPageNo + 1).Visible = False
MultiPage1.Pages(iPageNo + 2).Visible = False
TextBox1_Change'Run the TextBox1
Change event
Case 1
Label4.Caption = "Page 2 of 3"
MultiPage1.Pages(iPageNo + 1).Visible = False
MultiPage1.Pages(iPageNo - 1).Visible = False
CommandButton1.Enabled = True
CommandButton2.Enabled = False
TextBox2_Change'Run the TextBox2
Change event
Case 2
Label4.Caption = "Page 3 of 3"
MultiPage1.Pages(iPageNo - 1).Visible = False
MultiPage1.Pages(iPageNo - 2).Visible = False
MultiPage1.Pages(2).Visible = True
CommandButton2.Enabled = False
End Select
End Sub
I have purposely not commented the code very much as I would like you to take the time to read through it and see if you can see what is happening. It would pay greatly if you actually commented it for your own benefit.
Run the UserForm now to give it a test run. You should see that the user cannot skip a step and/or leave one blank, except for TextBox3 (Country of Birth). We will deal with this next.
Add another CommandButton (CommandButton3) to the UserForm , change its Caption to Enter and change its Enabled Property to False. Now add one more CommandButton (CommandButton4) and change it's Caption to Cancel.
For the Cancel button use this code:
Private Sub CommandButton4_Click()
Unload Me
End Sub
For the Enter button use this code:
Private Sub CommandButton3_Click()
'Enter all data onto active sheet
'Find the last cell in column "A" and offset 1 row
With Range("A65536").End(xlUp).Offset(1, 0)
.Value = TextBox1.Text
.Offset(0, 1).Value = TextBox2.Value
.Offset(0, 2).Value = TextBox3.Text
End With
'Set all controls back to default conditions
Unload Me
UserForm1.Show
End Sub
As you may have noticed the code for the Enter button will be entering the data onto the active sheet starting from the last cell in column A and moving across to column C. So you will need to place some headings into A1:C1 of any sheet. You have also no doubt noticed the unloading of the UserForm and then the immediate showing of it again. This is the quickest and easiest method to set all UserForm controls back to their defaults. If we used Me.Show we would get an "Automation error!" this is simply because the UserForm is no longer in memory and so Excel will not recognise what Me refers to
The only thing we must do now is enable our Enter button once they have some data in the Country of Birth TextBox.
For TextBox3 use this code:
Private Sub TextBox3_Change()
'Enter button enable/disable
If TextBox3.Value = "" Then
CommandButton3.Enabled = False
Else
CommandButton3.Enabled = True
End If
End Sub
Complete! You have now designed your very first Wizard style UserForm so give it a try.
Naturally this Wizard is far from fool-proof as we have no error checking to ensure the data they enter is valid or not. This we would could do each time they clicked the Next or Back button. for example we would need to ensure that they enter a number for their age and also that the number is not unrealistic. This could be done like this.
Private Sub CommandButton2_Click()
'Determine which page they are on _
and error check accordingly
Select Case Me.MultiPage1.Value
Case 0 'Name
'Some Code
Case 1 'Age
If Not IsNumeric(TextBox2.Value) Then
MsgBox "Age must be numeric"
TextBox2.SetFocus 'Place them back in
Exit Sub 'Do not advance
ElseIf TextBox2.Value < 18 Or TextBox2.Value > 100 Then
MsgBox "Your either too young or too old"
TextBox2.SetFocus 'Place them back in
Exit Sub 'Do not advance
End If
End Select
'Advance by one page
iPageNo = MultiPage1.Value + 1
MultiPage1.Pages(iPageNo).Visible = True
MultiPage1.Value = iPageNo
End Sub
It would be fair to say that "error checking" can be the
hardest part of developing a good UserForm. There is nothing worse than a
fancy looking UserForm that accepts any old values, it also causes users to
loose faith in your ability as a good developer. it is for this reason we will
dedicate the next lesson to just how to do this.
The exercise we have gone through in this lesson is also fully completed in the Workbook attachment. But please, don't cheat yourself and use it only. You will learn so much more by doing it for yourself.
As always, feel free to ask any questions at all.
©2002 ozgrid - microsoftexceltraining - David & Raina Hawley. All right reserved