Solved

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

Posted on 2013-12-15
3
1,822 Views
Last Modified: 2013-12-19
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
Comment
Question by:brothertruffle880
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39720739
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39723533
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
 

Author Comment

by:brothertruffle880
ID: 39728733
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

Featured Post

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question