Hi,
I have a combobox in the userform. And I have a data range: (A2:A40).

I want the dropdown to read the range A2:A40.

That will help the user choose from the dropown list.

Now what if the range gets smaller or larger. Do I have to change the
"Row Source" Everytime a change takes place?

I mean A40 is the destination, but it might be less in the future so
how do I
fix: A2:A40 once and for all.


Thanks,
Nawaf


--
countryfan_nt
------------------------------------------------------------------------
countryfan_nt's Profile: http://www.excelforum.com/member.php...o&userid=11051
View this thread: http://www.excelforum.com/showthread...hreadid=386366

Hi,
Try this:

Private Sub UserForm_Initialize()
lr = Cells(Rows.Count, "A").End(xlUp).Row ' Last row
ComboBox1.RowSource = "a2:a" & lr
End Sub

HTH

"countryfan_nt" wrote:


Hi,
I have a combobox in the userform. And I have a data range: (A2:A40).

I want the dropdown to read the range A2:A40.

That will help the user choose from the dropown list.

Now what if the range gets smaller or larger. Do I have to change the
"Row Source" Everytime a change takes place?

I mean A40 is the destination, but it might be less in the future so
how do I
fix: A2:A40 once and for all.


Thanks,
Nawaf


--
countryfan_nt
------------------------------------------------------------------------
countryfan_nt's Profile: http://www.excelforum.com/member.php...o&userid=11051
View this thread: http://www.excelforum.com/showthread...hreadid=386366

Thanks,

But, When I run the code I get "Compile error: Varriable not defined".
And "lr =" is highlighted. What Am I missing? Or what did I do wrong?

Nawaf


--
countryfan_nt
------------------------------------------------------------------------
countryfan_nt's Profile: http://www.excelforum.com/member.php...o&userid=11051
View this thread: http://www.excelforum.com/showthread...hreadid=386366

Add "Dim lr as Long" to the macro as I take it you have (correctly) an
"Option explicit" statement in you code.

HTH

"countryfan_nt" wrote:


Thanks,

But, When I run the code I get "Compile error: Varriable not defined".
And "lr =" is highlighted. What Am I missing? Or what did I do wrong?

Nawaf


--
countryfan_nt
------------------------------------------------------------------------
countryfan_nt's Profile: http://www.excelforum.com/member.php...o&userid=11051
View this thread: http://www.excelforum.com/showthread...hreadid=386366

Ok 2 more questions:

1: Do I need to specify the sheet name in the code because A2 is vague
without mentioning the sheet name. The sheet name is "LookupLists".

2: Where do I place the code. I mean should it be inside the comboBox
"View Code"


--
countryfan_nt
------------------------------------------------------------------------
countryfan_nt's Profile: http://www.excelforum.com/member.php...o&userid=11051
View this thread: http://www.excelforum.com/showthread...hreadid=386366