rowsource type mismatch

[Westicle]

Sorry to ask a quesion I know has been answered before but I can't find it.

I get a type mismatch with the following code.

Sub GetData()

UserForm1.ComboBox1.RowSource = Worksheets("Sheet1").Range("A1:A10")

End Sub

If I just use RowSource "A1:A10" then it works fine but I need to say which sheet to take it from.

 

[PHV]

Have you tried this ?
UserForm1.ComboBox1.RowSource = Worksheets("Sheet1").Range("A1:A10").Address

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Westicle]

I don't get a type mismatch now but it's picking up the range on the active sheet and not the sheet i'm specifying.

 

[PHV]

And what about this ?
UserForm1.ComboBox1.RowSource = Worksheets("Sheet1").Range("A1:A10").Address(External:=True)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Westicle]

That works perfectly.

Thanks for your help PH.

Westicle.

 

[Nikita6003]

Westicle,

Try changing the address to INclude the sheetname:
UserForm1.ComboBox1.RowSource = Worksheets("Sheet1").Name & "!" & Worksheets("Sheet1").Range("A1:A10").Address

HTH


Cheers
Nikki
Look, mommy, I'm flying!

 

[Nikita6003]

Apologies

hadn't seen the q was already answered



Cheers
Nikki
Look, mommy, I'm flying!