question about the ControlSource of a ComboBox

[delaney_55]

I have a column 'A' in my excel worksheet with the header 'Names' that
lists the names of all the people in a company (one name on each row).
Eg. A2 contains the data 'Bill', A3 contains 'Sal', A4 has 'Kim'
etc...

If I want a ComboBox to display all of these names I figured I could
modify the ControlSource property of the ComboBox by changing it to
something like 'Sheet1!A2' but that only adds the name in cell A2 to
the ComboBox. Is there any way to add the whole list of names to the
ComboBox? Note: the list of names keeps changing dynamically so I can't
do something like 'Sheet1!A2-A50'
Is there a way to reference the entire column?

Thanks for any help

 

[Ramses]

Hi

I try to explain a valuable way


Insert - Name - Define
For Name type (without quotation marks ): "Range_of_Name"
Refers to: =INDIRECT("Sheets1!A1A" &COUNTA(Sheets1!A:A)

...and add this Code into your Combobox

Private Sub Combobox1_GotFocus()
Me.Combobox1.ListfillRange = "Range_of_Name"
End Sub

Now you should get a whole List of names without blank fields

Best regards from switzerland ;-

 

[delaney_55]

many thanks for the quick reply!

:cool