How Do I Load a ComboBox From a Dynamic Range

[Guest]

Hello,

I want to use a ComboBox on Sheet1 that uses a list of Customers on Sheet2
for its data source. The Customer list is currently 188 names long and
growing (Ranges D18199). If I use the ListFillRange property the range
doesn't adjust as I add new customers to Sheet2 - It simply moves down
(D19200) ignoring the new addition(s). Is there a way to keep this
ComboBox dynamic?

 

[kfogle]

First, make your Customer List into a in Excel List by selecting the
range which includes the customer names and choose...

Data Menu > List > Create List

Check the box whether you have Headers or not. Click OK.

Click inside your new list. You should notice a blue border and an
extra cell at the bottom that contains a blue asterisk.

Second, select all the cells that contain customers and include the
blue asterisk in the selection. Name that range.

Third, use that Named Range to populate your combo box. When you add
customers, do so by first selecting the cell containing the blue
asterisk. You will notice that your Named Range will automatically
expand and your new customer will be added to your combo box.