Advanced Controls - Our Example

I'm going to assume that you've already read my two previous tutorials on user forms, namely:

• how to draw user forms; and

• how to write code for user forms, to interact with their events.

The aim of this tutorial is to show you how to work with combo boxes, list boxes and the like.

A Souped-Up Drink Order Form

The examples in this tutorial will all refer to the following form:

One of the three pages of the form is shown here.

You can download the workbook containing this example user form, complete with all the attached code.

Creating a MultiPage Control

 A multipage control allows you to get lots of information into a single form:

Page 1	Page 2	Page 3

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:

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:

In the code above, the line:

makes the first page of the multipage the current one (the pages are numbered from 0).  Without it, the line:

would crash the macro, because you can't go to a control which isn't on the active page of the MultiPage.

Advantages of 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

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.

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.

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:

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 tutorial).  For example:

In the code above, we look at the number of characters in the drink chosen in the combo box, using the Text property.

Why Use a Multi-Column List

It can sometimes be useful to display one thing in a combo or list box, but store another.  Here's a case study where we want to get the id field of the person ordering a drink, but display the name:

We have a list of records from a database query or view, and want to allow a user to choose a person.

We want to be able to choose by name, but store by number:

You choose the person ...	... but store the name

Combobox or Listbox Properties for Multiple Columns

To get our example above to work you need to set some properties of the combo or list box:

We'll need to set the numbered properties shown above.

Here's what the numbered properties are used for:

 Writing Code to Populate the Combo or List Box

Having created a combo (or list) box and set its main properties as above, you can now use ADO code to get the underlying records and add them into the list of possible values.  Here's an example:

Notice that you add each item to the list, and then set the values for the item's first and second columns (ie the person's name and id). 

Making a ListBox Multi-Select

The previous two parts of this tutorial have considered general features for combo and list boxes, but there is one thing you can do with a listbox that you can't do with a combo box: allow a user to select lots of records at the same time. To do this, first make your listbox multi-select:

You can choose 3 possible values for a MultiSelect listbox.  The difference between frmMultiSelectMulti and frmMultiSelectExtended (who thinks up these names?) is that with the latter you can click and drag to select lots of adjacent items at the same time.

You can then click on any item to select (or deselect) it:

Here we have selected at least 3 departments (more may be shown selected if we scroll down).

Picking Up on Items Selected in Code

You can loop over the items in the Selected array to find out which items were chosen:

Notice here that the ListCount property gives the number of items in the list (numbered from 1), but the Selected array is numbered from 0.

Spin Buttons

SpinButtons at first glance look clunky and unnecessary:

The SpinButton allows the user to change the number of sugars ordered.

However, the reason that they are so useful is that they allow you to prevent users typing in numbers directly:

The textbox for the number of sugars has the Enabled property set to False - this means that a user can't change the value directly.

To create a SpinButton, just click on the tool and then click where you want it to go:

Don't confuse the SpinButton tool with the scroll bar tool ...

A SpinButton usually sits next to the textbox whose value it will affect:

Here the SpinButton will control the value in the txtSugar textbox.

That's the easy part.  When you double-click on the SpinButton to write code, you get an odd default event:

It's more likely that you would attach code to the SpinDown and SpinUp events.

Writing a Generic Procedure to Handle Spinning Up and Down

It's good programming practice to write a single procedure to handle both possible spin directions.  For our example, this could be:

Here we'll pass in the number of sugars to add (this can be negative).  The routine makes sure that you don't set the number of sugars to be less than 0.

Attaching Code to the SpinButton Events

If you've written a generic procedure as above, you just need to call it twice - once when you spin up, and once when you spin down:

When you click on the down arrow of the SpinButton, for example, it will call the AddSugar routine to add -1 sugars.

Adding CheckBoxes

 A check box can have two possible values: True or False.

You either do want a biscuit, or you don't!

To add a checkbox to a form, use the CheckBox tool:

Click on the CheckBox tool to add it to a form.

Formatting a CheckBox

A checkbox comes with associated text, which can appear either to the right or left of the box itself:

The Alignment property doesn't align the text within its container, but instead specified whether it should appear to the right or left of the tick box.

To show how this works, consider a check box with the following properties:

Here's what this would look like:

The text appears to the right of the tick box, but is left aligned.

Picking Up on a Checkbox's Value

The Value property of a checkbox tells you whether it has been ticked or not:

This is short-hand for:

Option Buttons

An option button (sometimes called a radio button) allows you to choose between mutually exclusive possibilities:

You can have no milk, skimmed or normal milk, but you must choose one and one only possibility.

In a group of option buttons, each one can be True or False, but at any one time only one button can be True:

Here the OptNone option button has a value of False initially.  As soon as you select None as your milk choice - and thereby set the value of OptNone to True - the value of the other option buttons in the frame will automatically be set to False.

Adding Option Buttons using a Frame

There are two ways to add option buttons: either as part of a frame, or as part of an option group.  To add option buttons to a frame, first add the frame:

Click on the Frame tool ...	... to add a frame

You can then click on the option button tool to add buttons to the frame one by one:

Click on the option button ...	... then click inside the frame

Adding Option Buttons in a Group

Option buttons don't have to belong to a group.  Instead, you can add the option buttons to a form, then set their GroupName property:

Select the option buttons to change their GroupName property (or you can set it for each button individually).

Type in a value for the GroupName property (here we've set it to grpMilkChoices). Because no other option buttons use the same group name, the buttons will automatically now cancel each other when clicked.

Coding Option Buttons

The code to manage option buttons can be a bit messy.  Here's our example code:

Adding another option button is not only going to mess up the form, but also require more lines of code.  Like I say - combo boxes are easier! 

And with that, I've reached the end of this tutorial on advanced controls!