563 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Advanced controls in userforms with VBA macros
Part three 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.
Combo boxes and list boxes work in almost exactly the same way in user forms. Here's an example of each:
|Example of a listbox||Example of a combo box|
Combo boxes have two advantages over a listbox:
On the other hand, listboxes allow you to choose several items at once, which combo boxes don't allow.
Use combo boxes (or listboxes) wherever possible on a user form, since it makes validating data so much easier, as a user is restricted to a set of choices.
There are two ways to do this: by setting the RowSource property, or in code. The first way is easier! First create a range of the possible values for a combo or listbox:
This range is called Drinks, and contains all possible choices (here's more on how range names work).
You should then select the combo or list box:
Select the combo or list box to change its properties.
Finally, you can type the name of the range as the RowSource for the combo or listbox:
Note that you have to type in the range name - this is one of the only times in Excel where you can not press the F3 key to bring up a list of possible names. The reason for this, of course, is that you're not in Excel, but in the separate UserForms application.
You don't have to wait till you run the form to see the results - you can click on the drop arrow of a combo while designing your view to see the values listed.
There will be times when it's more convenient to add items to a combo or list box using code - here is a sample which would produce the following combo box:
We'll add in the items in the Drinks range above one by one, then remove the tea.
Here is the code to do this, which will run whenever you load the form:
Private Sub UserForm_Initialize()
'on first showing the form, populate drop list of drinks
'first clear any existing items
'now add in items from range
Dim DrinkRange As Range
Dim DrinkCell As Range
Set DrinkRange = Range("Drinks")
For Each DrinkCell In DrinkRange
'just for fun, remove tea (the second item)
Note that you can't mix and match: if you've set a RowSource property, the code above will crash with an unspecified error!
You can use either the Text or the Value properties of a combo or list box to find out what the user chose (the difference only becomes apparent when you use multi-column lists, as shown in the next part of this blog). For example:
'check drink chosen
If Len(Me.cmbDrink.Text) = 0 Then
'if not, go to second page and report error
MultiPage1.Value = 1
MsgBox "You must specify a drink!"
In the code above, we look at the number of characters in the drink chosen in the combo box, using the Text property.
|Parts of this blog|
25 Aytoun Street