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:
To place the data from the list-box1 into the column 2 of the worksheet we use the code:


Further reading:
Populate Userform ListBox or ComboBox

2 thoughts on “Populate ListBox on Excel User Form Using Named Range

  1. I haven’t had a creamy cabbage soup for years–yummy! thanks for the recipe but I’ve never heard it being paired with Polish sausage before–that’s usually a sauerkraut and sausage soupish dish which uses a lot of bay leaf.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.