asked on

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 -

?rngLogRange.Address(external:=True)

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

Norie

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?

RzzB

ASKER

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.

Norie

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.

RzzB

ASKER

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.

Norie

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.

RzzB

ASKER

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.

Norie

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

RzzB

ASKER

Hmm...

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.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RzzB

ASKER

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.

Norie

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.

RzzB

ASKER

Ahhh haaa... Yes a good idea.. a bit of a kludge but gets the end result.