Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.
 
Prepared by David Hawley of OzGrid Business Applications.
Specialists in Microsoft Excel and VBA for Excel
Web: www.ozgrid.com
Email: davidh@ozgrid.com

Download Time Saving VBA Solutions

Filling UserForm Controls

The vast majority of UserForms that are designed within Excel are used so users can easily select and input data. This also ensures that any entries that are entered into a spreadsheet are within the requirements needed. Possibly the best way to achieve both of these requirements is to supply a list of entries for the user to chose from. Excel has many Controls that can be placed on a UserForm that can make this not only easy for the user, but also for the designer of the UserForm. The two most useful Controls for this are the “ComboBox” and the “ListBox”. Let’s look at each of these Controls.

ComboBox

A ComboBox is so called because it combines the features of two other Controls. These are the TextBox and the ListBox. While it will allow a user to select an existing entry from a list of entries, as with a ListBox, it will also allow the user to enter a new entry, as with a TextBox.

The default Property for a ComboBox is the Value property.

The default Event for a ComboBox is the Click event.

A ComboBox will only display one row of data at any one time unless the user selects the drop arrow on the right of the ComboBox. The rows of data can then be between 1 and the total number of rows in the ComboBox.  The default is eight. This is set by the ListRows Property of the ComboBox.

ListBox

A ListBox is quite similar to a ComboBox but has three important differences. The first is that we can have a ListBox display a specified number of rows without user intervention. The second is we can set a ListBox so that a user can select more than one item at a time. The third is that a user cannot directly enter any new values into the ListBox.

The default Property for a ListBox is the Value property.

 The default Event for a ListBox is the Click event.  

Which One To Use?

While it is true that both the ListBox and the ComboBox have many similar Properties and Events, there are times that a ListBox is called for and times when a ComboBox is called for. It is up to us as the designer to make the correct decision.

The fact that a ListBox can display more than one row at a time is really more of a cosmetic effect than anything else. So this should really be our last reason for using a ListBox over a ComboBox or vice versa. The two differences that should dictate our decision are:

 

  1. Will the user need to type in their own entries.

  2. Will the user need to be able to select more than one entry at a time.

If the answer is yes to number one, then it’s a probably a ComboBox we need. If the answer is yes to number two then it’s probably a ListBox we need.  

Putting Data Into Our Controls

Once we have made our decision on which Control we are going to use we next need to decide which Method we are going to use to fill our Control with data. This is usually quite a simple decision and we will nearly always use a range of Worksheet cells to hold all our data and refer our Control to this range. Ideally, the Worksheet we use should be hidden from view. We can do this via Window>Hide or via VBA using the Worksheets Visible Property. eg;


Sheet1.Visible=xlVeryHidden


 

I would opt for the latter in most cases.  As I have said, we will usually opt for storing our data within a range of cells. This means we can easily add data to the range either manually or via VBA. The other option we have is to use the AddItem Property and I will discuss this after we have looked at the method we would use to refer to a range of cells. This is done via the RowSource Property.

Row Source - Applies to both a ComboBox and ListBox

A valid setting for the RowSource Property is a String. The String that we use would be either a valid cell address or a valid range name.  


ComboBox1.RowSource= "A1:A10"

 

ComboBox1.RowSource="Sheet2!A1:D10"

 

ComboBox1.RowSource="MyRange"


All of the above are valid settings for the RowSource Property. The RowSource can be set at either Run-time or Design-time. If the RowSource were set at Design-time we would enter our String into the Property window of the ComboBox or ListBox without quotation marks, eg;

A1:A10  

Or

Sheet2!A1:D10

Or

MyRange

As you can see the range we specify can be either a single column or multiple columns. I will explain this further later on when we look at the column setting Properties of both Controls.

AddItem - Applies to both a ComboBox and ListBox

This Property can only be set at Run-time. If the ListBox or ComboBox has only one column then AddItem will add an item to the ListBox or ComboBox list. If the ListBox or ComboBox has more than one column then AddItem will add a new row to the ListBox or ComboBox list

The syntax for the AddItem Property Is: [ item [,  varIndex]]  Both arguments are optional. The item is used to specify the item or the row to add. The number of the first item or row is always 0 (zero), the second is 1, and the third is 2 and so on…. The varIndex is used to specify the position within the Control where the “item” is to be placed. If you had a ComboBox that contained a ten row list and you used the AddItem to add another item you could use


ComboBox1.AddItem "Horse", 5


This would add the text “Horse” to our ComboBox as the fifth item or row in the list. If we had omitted the varIndex from the AddItem then the text “Horse” would be placed as the last item or row in the list.

The AddItem is best suited if

For most other cases the RowSource Property is better suited.

When To Put Data In Our Control?

Now we have decided which Control to use and where our data is going to come from, we next need to decide when we are going to fill our Control. We can have this done at Run-time or Design-time. If our data were being read from a Worksheet range we would most likely do this at Design-time. However if we are going to use the AddItem method we will be filling our Control at Run-time. If this is the case you will probably find that most often you will want the Control filled with the relevant data whenever the UserForm that is housing our Control is Initialized by the user. When this is the case, all we need to do is drop our code that will fill our ListBox or ComboBox into the UserForm_Initialize() Event.

There may be times when you want to alter the list or even replace it with new data while the user has the UserForm open. If you do need to replace the list that is in a ListBox or ComboBox you must clear the old data out first, else you will end up with two lists within the same Control. The way we can do this is by using the Clear Method. One thing to be aware of with this Method is that a Run-time error will be generated if you attempt to use the Clear Method on a ListBox or ComboBox that does not contain any data or is Bound* to a Data source**.

*Bound

Describes a control whose contents are associated with a particular data source, such as a cell or cell range in a worksheet.


**Data Source

The location of data to which a control is bound, for example, a cell in a worksheet. The current value of the data source can be stored in the Value property of a control. However, the control does not store the data; it only displays the information that is stored in the data source. For this reason we should always use the On Error Resume Next Statement to prevent a possible Run-time error and the On Error GoTo 0 to reset it. See below:


On Error Resume Next

ComboBox1.Clear

On Error GoTo 0


Lets now look at the settings we can use to specify the columns in our Control.

 

ColumnCount - Applies to both a ComboBox and ListBox

The ColumnCount Property is used to specify how many columns will be displayed in our ComboBox or ListBox.

A valid setting for the ColumnCount Property is a “Long” equal to or greater than -1. We can set the ColumnCount at both Run-time and Design-time. If we specify 0 (zero) then no columns are displayed. If we specify -1 then all available columns are displayed. This means that if we had set the RowSource Property to “A1:C10” and then set our ColumnCount to -1 the Control would display three columns.

If our Control has an *unbound  Data source then the ColumnCount has a 10 column limit, ie; 0-9

unbound is described by the Excel help as:

Describes a control that is not related to a Worksheet cell. In contrast, a bound control is a data source for a Worksheet cell that provides access to display and edit the value of a control.

Once we have set our ColumnCount we may need to specify the width of each column. We do this by using the ColumnWidth Property.

ColumnWidth - Applies to both a ComboBox and ListBox

The ColumnWidth Property is used to set the width of each column in a ListBox or ComboBox that have a ColumnCount other than 0 (zero)

The ColumnWidth takes a String as it’s setting. It can be set at both Run-time and Design-time. If we leave this as it’s default (blank) or set it to “-1” the width of the column will be a calculated width as set by Excel. Using a setting of “0” will hide the column. Any other setting will specify the column width of the column it is applied to.  To separate column widths use the list separator as set in the Regional Settings of the Windows control panel. This is often a semicolon (;)  The text below is from the Excel help

Settings

To separate column entries, use semicolons (;) as list separators. In Windows, use the list separator specified in the Regional Settings section of the Windows Control Panel to change this value.

Any or all of the ColumnWidths property settings can be blank. You create a blank setting by typing a list separator without a preceding value.

If you specify a –1 in the property page, the displayed value in the property page is a blank.

To calculate column widths when ColumnWidths is blank or –1, the width of the control is divided equally among all columns of the list. If the sum of the specified column widths exceeds the width of the control, the list is left-aligned within the control and one or more of the rightmost columns are not displayed. Users can scroll the list using the horizontal scroll bar to display the rightmost columns.

The minimum calculated column width is 72 points (1 inch). To produce columns narrower than this, you must specify the width explicitly.

Unless specified otherwise, column widths are measured in points. To specify another unit of measure, include the units as part of the values. The following examples specify column widths in several units of measure and describe how the various settings would fit in a three-column list box that is 4 inches wide.

Setting Effect
90;72;90 The first column is 90 points (1.25 inch); the second column is 72 points (1 inch); the third column is 90 points.
6 cm;0;6 cm The first column is 6 centimeters; the second column is hidden; the third column is 6 centimeters. Because part of the third column is visible, a horizontal scroll bar appears.
1.5 in;0;2.5 in The first column is 1.5 inches, the second column is hidden, and the third column is 2.5 inches.
2 in;;2 in The first column is 2 inches, the second column is 1 inch (default), and the third column is 2 inches. Because only half of the third column is visible, a horizontal scroll bar appears.
(Blank) All three columns are the same width (1.33 inches).

Remarks

In a combo box, the system displays the column designated by the TextColumn property in the text box portion of the control.

The other Property we will look at is the ColumnHeads Property.

ColumnHeads -  Applies to both a ComboBox and ListBox

This property allows us to have headings for the columns in a multi-column ComboBox and ListBox.

A valid setting for this is a Boolean (True or False).  The default is False. If we set this Property to True our column headings are frozen in much the same way as headings are frozen when we apply Freeze Panes on a Worksheet.

Let’s assume we have headings on a Worksheet in the range A1:C1 and we have set the ColumnHeads Property of a ListBox to True. To have our headings displayed in the ListBox we would need to set the RowSource Property to:


ListBox1.RowSource="A2:C10"


In other words we need to omit the first row when we fill our list then Excel will use the row immediately above our RowSource as the headings. There are many other Properties that we can set in a ListBox and ComboBox, but these are arguably the most common or most useful. The help file in Excel gives good detail on all Control Properties! To access help on particular Controls Properties, select the relevant Property name in the Properties Window of the Control and push F1.

Filling a ListBox and ComboBox Using Loops

While using the RowSource Property referring to a range is the most likely way to fill a ListBox and ComboBox and probably the easiest way. There are times when the AddItem method is a better option and in some cases the only option. Let’s say that you want to fill a ComboBox or ListBox with times that cover the previous 24 hour period at 15 minute intervals. You could of course use the NOW() Function on a hidden Worksheet and increment it by the needed increment, ie;


A1 = NOW()

A2 = A1+0.0104166666666667

A3= A2+0.0104166666666667


And so on…… But as you can see this can possibly get a bit messy, so let’s use a Loop instead.


Private Sub UserForm_Initialize()
Dim dMyTime As Date

  dMyTime = Now

    Do Until dMyTime >= Now + 1
          dMyTime = dMyTime + 0.010417
         ListBox1.AddItem (Format(dMyTime, “hh:mm”))
  Loop
   
End  Sub

This Loop will fill our ListBox with times that are incremented at 15 minute intervals over a 24 hour period. The code will only run when the UserForm that houses the ListBox is initialised. We could of course have the code run via a CommandButton or many other methods.

Summary

So as you can see there does need to be some foresight as to which Control is best suited to a particular situation. At times you may even use both the ComboBox and ListBox. Whenever possible try and use the RowSource Property to fill your Control as it is much easier to add, modify and change the list.

 

 

 


Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation®.

Prepared by David Hawley of OzGrid Business Applications.
Specialists in Microsoft Excel and VBA for Excel
Web: www.ozgrid.com
Email:

©2002 ozgrid - microsoftexceltraining - David & Raina Hawley. All right reserved

: created: 30.Sep 2001 : : hans : san remo wa :
website design by: aardvark internet publishing, wa [ AVIP.com.au ]