- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
ADVANCED CONTROLS IN USERFORMS WITH VBA MACROS
Part two of a nine-part series of blogs
The two previous parts of this mini-blog have shown how to draw forms and how to write code to handle form events. This final part shows how to add some of the more exotic controls to user forms, like combo boxes, list boxes, multipage controls, spinners and option buttons.
- Advanced Controls - Our Example
- Multipage Controls (this article)
- Combo boxes (and list boxes)
- Multiple Column Combo Boxes and Listboxes
- Multi-select ListBoxes
- SpinButtons (Spinners)
- Check Boxes
- Option Buttons (Radio Buttons)
- The Calendar Control
This blog is part of our Excel VBA tutorial. Wise Owl's main business is running Excel, VBA and other courses for businesses.
Multipage Controls
A multipage control allows you to get lots of information into a single form:
|
|
|
| Page 1 | Page 2 | Page 3 |
A TabStrip control, by contrast, is useless - for a TabStrip every "page" of the form contains the same boxes, so they can only be used to display records from a table.
Creating a MultiPage Control
Make sure that you use the MultiPage (not the TabStrip) control!
Click on the MultiPage tool, and click where you want it to go on your form.
You can then insert, move, rename and delete pages using the right mouse button:
Right-click on the page tabs at the top of the control to add new pages or rename or move existing ones in a fairly obvious way.
How MultiPage Controls Work
In a multipage control, if you paste or add a control onto a page, it belongs to that page; otherwise, it belongs to the form:
In this form:
- Controls in the red box were added to the About you tab and hence belong to it.
- Controls in the blue box were added to the form itself, and will stay visible whichever tab you click on.
Each page of a multipage control has its own tab order:
Here we're looking at the tab order for the About you page of the multipage control. The other pages will have their own tab orders.
Setting Focus to a Control on a Multipage
The only slight complication with a multipage is going to a control on it. You do this - surprisingly - by setting the Value property of the MultiPage:
'check user chosen a name
If Len(Me.txtName.Text) = 0 Then
'if not, go to first page ...
MultiPage1.Value = 0
'... and go to the offending text box
Me.txtName.SetFocus
'display error message and abort
MsgBox "You must say who is ordering drink!"
Exit Sub
End If
In the code above, the line:
'if not, go to first page ...
MultiPage1.Value = 0
makes the first page of the multipage the current one (the pages are numbered from 0). Without it, the line:
'... and go to the offending text box
Me.txtName.SetFocus
would crash the macro, because you can't go to a control which isn't on the active page of the MultiPage.
ADVANCED CONTROLS IN USERFORMS WITH VBA MACROS
Part two of a nine-part series of blogs
The two previous parts of this mini-blog have shown how to draw forms and how to write code to handle form events. This final part shows how to add some of the more exotic controls to user forms, like combo boxes, list boxes, multipage controls, spinners and option buttons.
- Advanced Controls - Our Example
- Multipage Controls (this article)
- Combo boxes (and list boxes)
- Multiple Column Combo Boxes and Listboxes
- Multi-select ListBoxes
- SpinButtons (Spinners)
- Check Boxes
- Option Buttons (Radio Buttons)
- The Calendar Control
This blog is part of our Excel VBA tutorial. Wise Owl's main business is running Excel, VBA and other courses for businesses.
Comments on this blog
This blog has 3 comments: