Type Mismatch, what am I doing wrong?

[vbaNumpty]

I am trying to populate a ListBox with a dynamic range, but I get a mismatch error on the .rowsource assignment. Hoping someone can shed some light on how I am supposed to assign the dynamic range to the row source.

Private Sub AddButton_Click()
'Add Item to Order
    Dim OrdSum As Worksheet
    Dim Addme As Range
    
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim StartCell As Range
    
    Set StartCell = Range("A1")
    
    ThisWorkbook.Sheets("Order Summary").UsedRange
    LastRow = StartCell.SpecialCells(xlCellTypeLastCell).Row
    LastColumn = StartCell.SpecialCells(xlCellTypeLastCell).Column
          
    Set OrdSum = Sheet4
    Set Addme = OrdSum.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)   
    
    With OrdSum
        
        Addme.Value = ProductBox.Value
        Addme.Offset(0, 1).Value = PID.Value
        Addme.Offset(0, 2).Value = CaseQty.Value
        Addme.Offset(0, 3).Value = PackSizeBox.Value
        Addme.Offset(0, 4).Value = StageBox.Value
        Addme.Offset(0, 5).Value = AssBox.Value
        Addme.Offset(0, 6).Value = ColourBox.Value
        Addme.Offset(0, 7).Value = IIf(OrderEntry.CoverSelect.Value = True, CoverBox.Value, "None")
        Addme.Offset(0, 8).Value = IIf(OrderEntry.OrnamentSelect.Value = True, OrnamentBox.Value, "")
        Addme.Offset(0, 9).Value = IIf(OrderEntry.UPCSelect.Value = True, UPCBox.Value, "")
        Addme.Offset(0, 10).Value = IIf(OrderEntry.CareTagSelect.Value = True, "Yes", "")
        Addme.Offset(0, 11).Value = IIf(OrderEntry.InsulationSelect.Value = True, "Yes", "")
        Addme.Offset(0, 12).Value = IIf(OrderEntry.SleeveSelect.Value = True, "Yes", "")
        Addme.Offset(0, 13).Value = NotesBox.Value
        
    End With
    
    Clear
        
    With ListBox1
        .ColumnCount = 14
        .ColumnHeads = True
        .ColumnWidths = "90,35,40,50,50,60,95,60,65,90,50,60,60,300"
        .RowSource = OrdSum.Range(StartCell, OrdSum.Cells(LastRow, LastColumn))
        .TopIndex = .ListCount - 1
    End With
    
End Sub

 

[RoryA]

Rowsource needs to be the address of the range.

 

[vbaNumpty]

Rowsource needs to be the address of the range.

How do I go about assigning the dynamic range to an address?

 

[RoryA]

.RowSource = "'" & OrdSum.Name & "'!" & OrdSum.Range(StartCell, OrdSum.Cells(LastRow, LastColumn)).Address

 

[vbaNumpty]

.RowSource = "'" & OrdSum.Name & "'!" & OrdSum.Range(StartCell, OrdSum.Cells(LastRow, LastColumn)).Address

Thank you!

 

[vbaNumpty]

.RowSource = "'" & OrdSum.Name & "'!" & OrdSum.Range(StartCell, OrdSum.Cells(LastRow, LastColumn)).Address

I have tried changing the way I do my dynamic range to account for if the values are empty with the following rowsource, but I now get Invalid property value. Could you point out what I am doing wrong?

The error only highlights the Rowsource argument after the Else line in the if statement.

Dim iRow As Long
iRow = [Counta(Order Summary!A:A)]

With ListBox1
        .ColumnCount = 14
        .ColumnHeads = True
        .ColumnWidths = "90,35,40,50,50,60,95,60,65,90,50,60,60,300"
       
        If iRow > 1 Then
            .RowSource = "Order Summary!A2:N" & iRow
        Else
            .RowSource = "Order Summary!A2:N2"
        End If
        '.RowSource = "'" & OrdSum.Name & "'!" & OrdSum.Range(StartCell, OrdSum.Cells(LastRow, LastColumn)).Address
               
        .TopIndex = .ListCount - 1
   
    End With

 

[RoryA]

The sheet name has spaces in it, so has to be enclosed in single quotes, just as it would in a formula - i.e. 'Order Summary'!A2:N2 not Order Summary!A2:N2

 

[vbaNumpty]

The sheet name has spaces in it, so has to be enclosed in single quotes, just as it would in a formula - i.e. 'Order Summary'!A2:N2 not Order Summary!A2:N2

Always getting owned by the syntax. Thanks a lot, appreciate your assistance.

 

[RoryA]

Glad we could help.