<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>

 

 

 

'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:

 

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.