Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.
 
Prepared by David Hawley of OzGrid Business Applications.
Specialists in Microsoft Excel and VBA for Excel
Web: www.ozgrid.com
Email: davidh@ozgrid.com

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

MultiPage Control

In this lesson we will look at the MultiPage control, arguably the most useful controls to use if your UserForm will be containing many different controls and/or you wish to have different controls associated with different aspects of your project.  The other thing that we can do with a MultiPage is make our UserForm behave in the same manner as any one of Excel's standard Wizards.  A Wizard by definition is an aid that steps you through a particular process.  An example of this would be the Pivot Table Wizard or Chart Wizard.
 
To attach a MultiPage control to a UserForm, we simply insert a UserForm into our Workbook, ensure the Control Toolbox is displayed, if it does not show by default when you insert the UserForm, simply select the UserForm and go to View>Toolbox, or click the hammer and spanner symbol on the Standard Toolbar.  The MultiPage control is normally situated on the third row down, second control in.  As with all controls, if you are unsure, just hover your mouse pointer over the control and the name will appear for you.  Click the MultiPage control and then click your UserForm and it will be embedded on there.  By default, your MultiPage will have two pages, Page1 and Page2.  To give yourself an idea of what this control does, simply ensure your UserForm (no the MultiPage) is selected and go to Run>Run sub/UserForm or push F5.  This will display the UserForm complete with a MultiPage control on it.  You will notice that by clicking Page1 and Page2, the MultiPage form control will change page.  Click the "X" on the top right hand side of the UserForm to close the UserForm.  As with all controls, it pays to give each one a meaningful name.  In other words, the default for the first MultiPage you use on a UserForm is MultiPage1, the second is MultiPage2 and so on, so change these to a name that will have some meaning to you, or the project they are being used in.  If two pages is not enough for your MultiPage, we can easily change this by right clicking on any one of the MultiPage tab names (eg; Page1) and then simply select NewPage.  You would do this for each page you want inserted.  You will also notice when you right click that you have the option to delete a page, rename a page or move a page.  Deleting a page speaks for itself, but if you select Rename, you will be prompted to give the page a caption name, which will replace the existing one, and an accelerator* key if you want one. 
 
* An accelerator key is a single character used as a shortcut for selecting an object.  Pressing the Alt key, followed by the accelerator key gives focus to the object and initiates one or more Events associated with the object.  The specific Event or Events initiated varies from one object to another.  If code is associated with an Event, it is processed when the Event is initiated.  Also known as keyboard accelerator, shortcut key and keyboard shortcut.
 
There is also a space to type some text for the ControlTipText property.  If you are not already familiar with this, the ControlTipText is activated whenever the user hovers the mouse over a control.  It then displays the text you have used for its property.
 
To see what I mean for both of these, right click on the tab Page2, select Rename, replace the caption Page2 with the word Test, type a lower-case a in the accelerator key and then in the ControlTipText box type "Push Alt + a to activate me". Then click back on Page1, and run the UserForm by pressing F5.  Hover your mouse pointer over the Test Page to see your TipText, then as the message sais, hold down the Alt key and press a and the page Test should be activated.  Again, click the top right "X" to close the UserForm.  The only other item we have on the right-click menu when right-clicking on a MultiPage tab name is the Move option, which simply changes the page order.  Using this is fairly self-explanatory, so I won't go into any detail.
 
The Properties that we have just changed, ie; Caption and Accelerator, can also be changed in the Property window of the MultiPage control.  If this is not visible already, right-click anywhere on the MultiPage control other than the tab names and select Properties.  Be aware though, that when you do this, many of the Properties you are looking at are only applicable to the page of the MultiPage that you have selected at the time.  Also, as with most Control Properties, we can change these Properties at Run-time via VBA code
 
 
Pages Collection
 
As mentioned above, it is very important to understand that each page in a MultiPage Control has its own Properties.  This simply means that if we change any one of its Properties at Run-time, we must tell Excel the page we are referring to.  The method we use to do this is by accessing the Pages Collection of the MultiPage Control.   This is very similar to the method you would use when accessing the Sheets Collection of a Workbook.  There are five methods we can use to access an individual page of a MultiPage.  These are:
 
  1. Numeric index
  2. Item Method
  3. Page Name
  4. Page Object
  5. SelectedItem
Let's look at each of these in turn!
 

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


 

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

 

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.

As always, feel free to ask any questions at all.

 

 


Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation®.

Prepared by David Hawley of OzGrid Business Applications.
Specialists in Microsoft Excel and VBA for Excel
Web: www.ozgrid.com
Email:

©2002 ozgrid - microsoftexceltraining - David & Raina Hawley. All right reserved

: created: 30.Sep 2001 : : hans : san remo wa :
website design by: aardvark internet publishing, wa [ AVIP.com.au ]