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.

  1. Advanced Controls - Our Example
  2. Multipage Controls
  3. Combo boxes (and list boxes) (this blog)
  4. Multiple Column Combo Boxes and Listboxes
  5. Multi-select ListBoxes
  6. SpinButtons (Spinners)
  7. Check Boxes
  8. Option Buttons (Radio Buttons)
  9. 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:

List box example Combo box example
Example of a listbox Example of a combo box

Advantages of Combo Boxes and List Boxes

Combo boxes have two advantages over a listbox:

  1. They take up less room on a form.
  2. 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:

A range of drink choices

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:

Combo box selected

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:

The RowSource property

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:

Combo box with no tea

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.

 

  1. Advanced Controls - Our Example
  2. Multipage Controls
  3. Combo boxes (and list boxes) (this blog)
  4. Multiple Column Combo Boxes and Listboxes
  5. Multi-select ListBoxes
  6. SpinButtons (Spinners)
  7. Check Boxes
  8. Option Buttons (Radio Buttons)
  9. The Calendar Control
This blog has 0 threads Add post