EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA 2 Lesson 9

 

Creating a Wizard With The MultiPage Control

<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

Workbook Download
    This is a zipped Excel Workbook to go with this lesson.

Visible Property
 

As  mentioned at the start of this lesson, the MultiPage control is ideal for creating a Wizard style form. To do this we can use the Visible Property of each Page on the UserForm. Imagine we had a 3 page MultiPage on our UserForm and each page had a TextBox (or any other control) that we wanted to the user fill in, but it was vital that they did this in the correct order. Let's design a simple one now and this will help you understand what I mean.

  1. In any Excel Workbook, open the VBE
  2. Insert a UserForm and then attach a MultiPage control to it.
  3. Ensure the MultiPage has 3 page tabs called Page1, Page2 and Page3
  4. On each page place a TextBox.
  5. Above each TextBox place a Label control
  6. In Label1 type the text: Your Name ? To do this, either do a slow double click or use the Caption Property in the Property window of the Label.
  7. In Label2 type the text: Your Age ? In Label3 type the text: Your Country of Birth ?
  8. Now at the bottom of the UserForm (not the MultiPage) place two CommandButtons parallel to each other. These will be the Next and Back buttons
  9. Change the Caption of the left most button to Next and the other to Back
  10. Change the Enabled Property of both buttons to False
  11. Now select Page3 and change it's Visible Property to False
  12. Do the same for Page2. Your pages will still be visible while in the VBE, but wont be once we run the UserForm.
  13. Now place another label at the top of the UserForm, change it's Caption to Page 1 of 3

Ok, to ensure we have the same controls and control names

  • My MultiPage is called MultiPage1 
  • My UserForm is UserForm1
  • My Name TextBox is TextBox1 one and It's Label Label1
  • My Age TextBox is TextBox2 one and It's Label Label2
  • My Birth TextBox is TextBox3 one and It's Label Label3
  • My Back button is CommandButton1
  • My Next button is CommandButton2
  • My Page * of * is Label4

 

Now, let's get into the fun stuff, the VBA code! As we will using the default Events for these controls, for all codes you only need double click the needed Control to access the Private Module of the UserForm. 

 
For Textbox1 place in this code:
 
Private Sub TextBox1_Change()

'Next button enable/disable
If TextBox1.Value = "" Then
    CommandButton2.Enabled = False
Else
    CommandButton2.Enabled = True
End If

End Sub

For Textbox2 place in this code:

 
Private Sub TextBox2_Change()

'Next button enable/disable
If TextBox2.Value = "" Then
    CommandButton2.Enabled = False
Else
    CommandButton2.Enabled = True
End If

End Sub

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.


Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX