<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>
Ozgrid, Experts in Microsoft Excel Spreadsheets

 Excel Training VBA 2 Lesson 5

Filling UserForm Controls

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX

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.

Information Helpful? Why Not Donate | Free Excel Help
<<Previous Lesson | NEXT LESSON>> | BACK TO EXCEL VBA LEVEL 2 TRAINING INDEX