BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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
- Combo boxes (and list boxes) (this blog)
- 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.
Posted by Andy Brown on 28 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Combo boxes (and list boxes)
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 |
Advantages of Combo Boxes and List Boxes
Combo boxes have two advantages over a listbox:
- They take up less room on a form.
- They allow you to use predictive text (when you type in Ch in the combo box above, it would take you to the item Chocolate). You can only search in a listbox by the first letter of each item.
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.
Populating the Rows of a Combo Box - the Easy Way
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.
Populating the Rows of a Combo Box - the Hard Way
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
cmbDrink.Clear
'now add in items from range
Dim DrinkRange As Range
Dim DrinkCell As Range
Set DrinkRange = Range("Drinks")
For Each DrinkCell In DrinkRange
cmbDrink.AddItem DrinkCell.Value
Next DrinkCell
'just for fun, remove tea (the second item)
Me.cmbDrink.RemoveItem (1)
End Sub
Note that you can't mix and match: if you've set a RowSource property, the code above will crash with an unspecified error!
Returning the Value of a Combo Box or List Box
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
Me.cmbDrink.SetFocus
MsgBox "You must specify a drink!"
Exit Sub
End If
In the code above, we look at the number of characters in the drink chosen in the combo box, using the Text property.