Solved

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

Posted on 2013-12-15
3
1,918 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

728 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