Populate ListBox on Excel User Form Using Named Range

How to populate a list-box in a user-form using a named range
This Excel training video describes how to use a named range to populate a list-box in a user-form with relevant data. The data in the named range is accessed via the ‘rowsource’ property from the properties windows of the list box.

We have learnt how to use a list-box on a user-form and populate it with data using arrays. Today we learn how to populate the list-box control with data using a range in an Excel worksheet that has been assigned a name – also known as a named range.
The command button that is used to transfer the data from the user-form to the Excel worksheet first finds the next blank or empty row where the data can be placed automatically. The VBA code for the first blank row is:
erow = ActiveSheet.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
Next we transfer data for the text-box and the list-boxes using appropriate cell addresses which take the row index number and the column index numbers as its parameters. So to place the name of the customer under the header Name we use the VBA code:
cells(erow,1)=TextBox1.Text
To place the data from the list-box1 into the column 2 of the worksheet we use the code:
cells(erow,2)=ListBox1.Value

 


Further reading:
Populate Userform ListBox or ComboBox

One thought on “Populate ListBox on Excel User Form Using Named Range

Leave a Reply

Your email address will not be published. Required fields are marked *