This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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.

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR