Fill combobox from different worksheet

[Ako_____]

Hi

I have a userform with a combobox. I want to fill the combobox from a datasheet I have in another workbook. I have tried to browse the internet but haven't found anything usefull.
Among numerous attempts I have tried the following code, but it still won’t work:

ComboBox1.RowSource = ('[Workbook.xlsx]Sheet1'!$B$1:$B6$)

Can somebody help me?

[Bob Phillips]

Is Workbook.xlsx open?

[Ako_____]

Does it make a big differense?
I would prefer that is was closed. However if it makes it a lot more complicated I could make it open!?!

[Bob Phillips]

I think it needs to be open.

[Ako_____]

Okay, then it will be open. Do you know how to make it work?

[Bob Phillips]

If the workbook is open, it should work as you have it.

[Ako_____]

Hi

For whatever reason I can't get the code to work. I'm using the following code but it keeps giving me an error message like the following:

Compile error:
Expected: expression

Private Sub UserForm_Initialize()

ComboBox1.RowSource = ('[Workbook.xlsx]Sheet1'!$B$1:$B$6)
ComboBox1.Style = fmStyleDropDownList
ComboBox1.ListRows = 8
ComboBox1.ListIndex = 0
End Sub

OBS! The third line of the code above should not be green as it is a part of the code. In my code the whole line turns red?!?



:dunno

[Tinbendr]

How about ...
ComboBox1.RowSource = "'[Workbook.xlsx]Sheet1'!$B$1:$B$6"

David

[Ako_____]

Unfortunately I can't get that to work either. When I run the code the following error message pops up:

Run-time error '380':
Could not set the RowSource property. Invalid property value

[Tinbendr]

Sorry, I could not get it to work on a closed workbook. (Works on an open one, though)

All the other examples I found always open the workbook first.

[Ako_____]

YES, I finally got it to work! (Still with the workbook open though)

Thanks a lot for your help and patience :thumb