Could not set the RowSource property when I open another workbook

[lattechic]

Hi Experts,

I have a macro enabled workbook and when I start the program I get "Run-time error 380 - could not set the rowsource property. Invalid property value" in my UserForm initialize. Line in error is when I set the rowsource below.

Private Sub UserForm_Initialize()

    WorkbookName = ThisWorkbook.Name
    Set wb = Workbooks(WorkbookName)
     Set ws = wb.Worksheets("DOASaveAddtlCharges")

    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    'set rowsource
    If LastRow = 1 Then
        ListBoxAddtlFees.RowSource = "DOASaveAddtlCharges!A2:C2"
    Else
        ListBoxAddtlFees.RowSource = "DOASaveAddtlCharges!A2:C" & LastRow
    End If

End sub

I am new to VBA. Please help.

Thanks in advance.

 

[dmt32]

Hi,
see if this update to your code does what you want

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim arr As Variant
   Dim lastrow As Long
    
    Set ws = ThisWorkbook.Worksheets("DOASaveAddtlCharges")
    lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      
    arr = ws.Cells(2, 1).Resize(lastrow, 3).Value
    If Not IsArray(arr) Then arr = Array(arr)
    
    With Me.ListBoxAddtlFees
        .ColumnCount = 3
        .List = arr
    End With


End Sub

Dave

 

[lattechic]

Thank you, Dave. I will try it tomorrow when I get in the office.