Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Controls > Description (3)
Deze pagina in het Nederlands

Using controls on worksheets

Detailed description of the controls (3)

ListBox

If you want to enable your user to select an option from a list you can use a list box. Of course you could also use a set of option buttons for this goal, but making a set of option buttons dynamic (for example if you want to be able to expand the number of choices) is cumbersome. If you have a limited set of available choices (rule of thumb: no more than 5), use option buttons. If there are more, use a list box or a combo box control.

You can either have the control pick up the list from a range of cells, or add the choices to the list box control using VBA. This is done by entering the corresponding information into the ListFillRange or Input range property. If your list of choices resides on a different worksheet from the one your list box is placed on, you must define a range name for the list. Do so by selecting the list and hitting control+F3. In Excel 2007 and up you then need to click the "Add" button. Enter a name for the list and click OK until you're back in Excel. After that, you can enter this new range name in the appropriate property of the control.

The second most important property is the LinkedCell (cell link), this cell will receive the result of selecting an item in the list box:

Opties van de keuzelijst

Fig 10, Two important propeties of the ActiveX list box control.

Opties keuzelijst formulier

Fig 11: Two important properties of the forms list box.

Note that the list box from the forms toolbar will show the index of the chosen item in the cell, whereas the control from the control toolbox returns the actual value to the cell. For the list box from the forms toolbar, use a formula like this one to get the actual value:

=INDEX(ListForComboAndList,C1)

You can set the list box to "Multi" or "Extended" to enable multiple selections. In that case, the linked cell will show either a zero for the list box from the forms toolbar or #N/A for the control toolbox list box control. You will have to use VBA to read what items have been selected and act accordingly.

ComboBox

A combobox is very useful when there are many values to choose from and when you only want to show the chosen item. With the combobox from the Control toolbox you can dynamically add items to the list -using VBA- when the user types a new item in the box instead of selecting an existing item. The combo box form the forms toolbar does not have this possibility: the user is limited to the choices available in the list.

The two important properties LinkedCell and ListFillRange operate in the exact same way as for the list box control.


 


Comments

All comments about this page:


Comment by: PAT CHOR (11/10/2015 10:05:05 AM)

I Have sum question for VBA Code
in USER Form Can use THis Code
    
Controls("Combobox" & I).clear

but in the work sheet can't use Above code

how to use code in the worksheet???

Thx....

 


Comment by: Jan Karel Pieterse (11/10/2015 5:23:52 PM)

Hi Pat,

Depends. What type of combobox is it?

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].