Solved

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

Posted on 2013-12-15
3
1,791 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Easy Excel formula needed 4 27
Close form "before" open 3 25
Input Data Using a unique ID from another sheet in Excel 3 16
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now