Excel Training

How to use a list-box on a user form

A List-Box on a user form can be used to create interesting surveys.
The real challenge is to populate the listbox with appropriate data that can be selected on running the form and the value can then be transferred to the Excel worksheet where it can be analyzed further. Apart from using looping methods like nested 'for loops' you can use the following 3 simple methods to populate the list box with appropriate data.

  1. Create a range on the Excel worksheet with the relevant data like name of cities, countries, qualifications, etc. Select the listbox on the user form and open the 'properties' window. Against the 'rowsource' enter the range of the cells that contain the data. In case the data is on another worksheet like sheet2, then you'll need to enter the sheet name also. Eg. 'Sheet2! A2:A5' without the quotes, of course.
  2. You can use a 'named' range. Just select the relevant data to be used in the list box in the worksheet and give it a name like 'location' in the 'Name Box'.
  3. The third method is to use arrays. For example, you can use the following code to populate the list-box:
    ListBox1.List=Array("New Delhi","New York", "Mumbai", "Hamburg","Phoenix")
    This method is lovely if you enjoy working with technical terms like arrays.


How to use a list box on a user form