• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2083
  • Last Modified:

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
0
brothertruffle880
Asked:
brothertruffle880
1 Solution
 
byundtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now