Invalid property value when setting RowSource

I am using Win 7 & Excel 365.

I am getting a "Could not set the RowSource property. Invalid property value." when trying to set the RowSource from a range from an external workbook.

Here is the code...

Sub DisplayLog()

  Dim lngLogLastRow As Integer
  Dim rngLogRange As Range

  'Get number of rows in the Log
  lngLogLastRow = shtGLog1.Range("A1").End(xlDown).Row

  'Set our range of the log from Row 3 to the bottom - 17 columns
  Set rngLogRange = shtGLog1.Range(shtGLog1.Cells(3, 1), shtGLog1.Cells(lngLogLastRow, 17))

  'Now apply the range address (external) to the RowSource
  UserForm1.lstLog.RowSource = rngLogRange.Address(external:=True)
  'Tidy up on the way out
  Set rngLogRange = Nothing

End Sub

Open in new window

using the Immediate window -


evaluates to...

'[CFAG Membership Log.xlsx]Sheet1'!$A$3:$Q$57

The Workbook is in another instance of Excel and is open. The application is hidden.

What am I doing wrong?
Thanks, Roy
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
I think the problem could be that the source workbook is in a separate instance of Excel.

Can you not open the source workbook in the same instance as the one in which the workbook with the userform is?
RzzBAuthor Commented:
The reason that I have it in a separate instance of Excel is that I want it to be hidden from the user. My understanding is that if I want it hidden, it can't be in the same instance of Excel as the userform.

I have googled this for ages and I have seen many posts doing this (setting RowSource from external range) but I just can't get mine to work.

I guess I could pull the data into a temp worksheet and set the RowSource from there but that's admitting defeat! I just can't see why it's not working.
NorieAnalyst Assistant Commented:
I thought you were hiding the instance of Excel the userform was in?

If you are you could open the source workbook in that instance and it won't be visible.

If you must use a separate instance try using List instead of Rowsource to populate.
UserForm1.lstLog.List  = rngLogRange.Value 

Open in new window

Note if you use List you can't have headers in the listbox.

Mind you once you've populated the listbox in this way you could close the source workbook if you wanted.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

RzzBAuthor Commented:
The Userform is visible and running in ThisWorksheet.  It is the main user interface for the application.
The data that I'm trying to get into the RowSource is in an external workbook that I want hidden. This external hidden workbook is a log the actions taken by the user. It gets opened at initialisation of the UserForm and closed when the user exits. This listbox allows the user to view the log.

Your suggestion to use List works - that's excellent.

Now I just need a way to get the headers in place! Is there some way to do that? Or is it just not possible if using List? If not, I'm back to trying to understand why setting RowSource doesn't work. I tried setting the RowSource using a temp worksheet in ThisWookbook to get just the headers, and then setting the List to the data from the external workbook but it wouldn't let me do that.
NorieAnalyst Assistant Commented:
There's no way to get headers other than using RowSource, and you can't use RowSource with a workbook open in another instance of Excel as the userform can't 'see' the source workbook.

You could transfer the data and headers to a temp worksheet.

If you adjust rngLogRange to include the header row this would copy the data and headers to a new, temporary worksheet.
Dim wsTemp As Worksheet

    Set wsTemp = Worksheets.Add

    With rngLogRange
        wsTemp.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

Open in new window

PS Couldn't you have the log in the same workbook as the userform? Perhaps on a very hidden worksheet.
RzzBAuthor Commented:
Thanks for your help on this Norie.

I don't want to put the log in ThisWorkbook. There are cases where I want to log actions that the user has taken but do not want to save workbook changes on exit. The log thus doesn't get saved. Having the log in an external workbook overcomes this problem.

I will go the temporary worksheet route.

I'm bemused as to why the UserForm can't "see" the source workbook. It can "see" it well enough to get it via List. Odd.
NorieAnalyst Assistant Commented:
if you have 2 separate instances of Excel they can't really 'see' each other or communicate directly.

For example you can't copy and paste between them, which is why I didn't use copy/paste in the code I posted for the temporary worksheet.

What happens when you try this code in the workbook with the userform?
MsgBox Workbooks("CFAG Membership Log.xlsx").Name

Open in new window

RzzBAuthor Commented:
Workbooks("CFAG Membership Log.xlsx").Name

Open in new window

throws an error because that workbook is not in this instance of excel application.

However if I qualify it with the correct application ...
xapGForLogging.Workbooks("CFAG Membership Log.xlsx").Name

Open in new window

it works just fine.
NorieAnalyst Assistant Commented:
To use RowSource the range you want to use needs to be in a workbook that is open in the same instance as the workbook with the userform.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RzzBAuthor Commented:
OK - I admit defeat :-)

I have it working, after a struggle, using the temp worksheet method. I had a struggle because when I assigned the range address to the RowSource I found that it required the Worksheet containing the data to be the active worksheet. Got there in the end - but not a very elegant solution. And I can't say I really understand why it's as it is. Ho humm... life goes on :-)

Norie - many thanks for your help.
NorieAnalyst Assistant Commented:
No problem.

Personally I avoid using RowSource for various reasons.

If I really want headers I'll either set up a bunch of labels just above the listbox or, and this is my preferred option, add a one row listbox above the 'data' listbox and populate it with the headers.
RzzBAuthor Commented:
Ahhh haaa... Yes a good idea.. a bit of a kludge but gets the end result.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.