Auto Editing the Row source.

[countryfan_nt]

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

[Toppers]

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
>
>

[countryfan_nt]

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

[Toppers]

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
>
>

[countryfan_nt]

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"