Excel VBA 2010 - List Box/Rowsource problem - Can't get to file where rowsource is located

I'm messing up some sort of syntax rule.

I'm trying to get cells a1:a10 in sheet2 in file "rowdata.xlsx" as a rowsource
I tried inputting [rowdata.xlsx]sheet2!a1:a10  but it didn't work.
VBA did not accept that!

Please help with syntax.  See graphic for what I have so far!err
brothertruffle880Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
byundtConnect With a Mentor Commented:
I'm assuming that you have a userform with a Listbox in workbook Book2.xlsm and want to point its RowSource property to cells in workbook Book1.xlsx.

I was able to succeed by creating a named range in the workbook with the userform that pointed to the cells in the other workbook. Its name was myRowSource and its RefersTo property was:
='[Book1.xlsx]Sheet1'!$A$1:$A$10

I then used the following code in the Userform_Initialize sub to set its properties:
Private Sub UserForm_Initialize()
ListBox1.RowSource = "Book2.xlsm!myRowSource"
End Sub

Open in new window

0
 
FaustulusCommented:
Your syntax for specifying the Rowsource is correct. Here are Microsoft's own instructions.
When I tested I found that only the tab's name will work, not the sheet's CodeName. I got an error when I specified the name of a tab that doesn't exist.
0
 
brothertruffle880Author Commented:
Hi Byundt:
THANK YOU!
Your file name syntax worked:  '[Book1.xlsx]Sheet1'!$A$1:$A$10

I made TWO mistakes!!!
1.  My syntax was wrong --which you corrected.  Thanks.
2.   An assumption of mine was wrong:  I thought I could use a row source if the book1.xlsx was CLOSED!  Wrong!  I had to open book1.xlsx in order for  '[Book1.xlsx]Sheet1'!$A$1:$A$10 to be accepted by the VBE field shown in my graphic above.
0
All Courses

From novice to tech pro — start learning today.