Form Control Combo Box 1

[aharris2693]

I am an intern and am trying to make a form that will update another form when changes are made. i have built the whole thing using control objects, but once I am gone, no one will know how to change any of the VBA, so I am trying to do it much simpler using the 'forms' objects. My question, finally, is I have a combo box whose input range comes from the second sheet in the workbook. This range will continuously get larger. Is there anyway to make the combo box update itself. I currently have the input range going past the end of the list, but this adds a lot of blank space at the bottom. Thanks for any help

 

[xlbo]

Use a dynamic range name:
Lets say your list is in a sheet called "Data", starting in row1, column A (as all good lists should ;-) )

Go
Insert>Name>Define

Give the range a name - lets call it ListA

In the textbox provided, instead of a range, enter:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),1)

This range will expand as data is entered into it. just use the range name as the data source for the combo box and it should be self populating

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[aharris2693]

Thanks again for all your help, it worked perfectly.