Could not set the RowSource property. Invalid property value

[borgtamer]

I have a spreadsheet with the name "S1" in a workbook with the name "W1" and have an addin with the name "A1".
In A1 there is a listbox in a user form.

I want to bind a range in S1 to the listbox in A1. This is the only way I can display more than 10 columns of S1 in the list box.

When I run the macro I get "Run-time Error 380: Could not set the RowSource property. Invalid property value."

The macro below is in the addin A1 user form UserForm. I cannot see what I am doing wrong so any help is appreciated. Thank you in advance.

Joe

Private Sub UserForm_initialize()
    'Display a range of cells in S1 in ListBox1
    Dim lastRow As Integer
    Dim firstRow As Integer
    Dim lastCol As String
    Dim rowNum As Integer
    Dim colNum As Integer
    Dim rng As String
    Dim rs As String
    Dim prefix As String
    
    With Workbooks("W1.xlsm").Sheets("S1")
        lastRow = .Cells(.Rows.Count, firstRow).End(xlUp).row
        lastCol = "O" 'For testing
        firstRow = 5 'For testing
        Me.ListBox1.ColumnCount = 15 'For testing
        Me.ListBox1.ColumnHeads = True
        
        rng = ".Range(" & Chr(34) & "A" & firstRow & ":" & lastCol & lastRow & Chr(34) & ").Address()"
        prefix = "Workbooks(" & Chr(34) & "W1.xlsm" & Chr(34) & ").Sheets(" & Chr(34) & "S1" & Chr(34) & ")"
        rs = prefix & rng
        Me.ListBox1.RowSource = rs
      End With
End Sub

 

[humdingaling]

i think your over complicating it

        lastCol = "O" 'For testing
        firstRow = 5 'For testing
        
        lastRow = .Cells(.Rows.Count, firstRow).End(xlUp).Row
        
        Me.ListBox1.ColumnCount = 15 'For testing
        Me.ListBox1.ColumnHeads = True
        
        rng = "A" & firstRow & ":" & lastCol & lastRow
        
        Me.ListBox1.RowSource = "S1!" & rng

also i think your last row needs to after first row otherwise how can you use first row before you set it?

ps
the syntax you have lastrow working
"firstrow" is actually asking what column it look under

 

[borgtamer]

Thank you so much humdingaling. It works perfectly.

 

[RoryA]

Also, you don't actually have to use Rowsource to get more than 10 columns, you just can't use AddItem:

Me.ListBox1.List = Sheets("S1").Range("A" & firstRow & ":" & lastCol & lastRow).Value

will work just fine (and is my preference, unless you really need column headers)