ListBox List Fails When Table Has One Row

[wsnyder]

Hi all,

Using Excel 365.

My code returns an error when the source Table has 1 Row.
How can I modify to handle whether the Table has 1 to n Rows?
Works great as-is as long as the Table has 2 or more rows.

Thanks
-w

Private Sub UserForm_Initialize()

    'Objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim lo As ListObject
        
    'Initialize
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("dataSuppliers")
        Set lo = ws.ListObjects(1)
        
    'Populate listbox
        If lo.DataBodyRange.Rows.Count = 1 Then
            'Do something
        Else
            lstBoxSuppliers.List = lo.DataBodyRange.Value
        End If
        
    'Tidy up
        Set lo = Nothing
        Set ws = Nothing
        Set wb = Nothing
        
End Sub

 

[Akuini]

I assume the table has only 1 column.
Try:

    'Populate listbox
    Dim va
        If lo.DataBodyRange.Rows.Count = 1 Then
            ReDim va(1 To 1, 1 To 1)
            va(1, 1) = lo.DataBodyRange.Value
            lstBoxSuppliers.List = va
        Else
            lstBoxSuppliers.List = lo.DataBodyRange.Value
        End If

 

[wsnyder]

Thanks Akuini,

Works great if there is only 1 Row in the Table.
However, as soon as I add a 2nd Row, it throws an error:

Run-time error '13':
Type Mismatch

Why is that?
Revised code snippet

    'Populate listbox
        If lo.DataBodyRange.Rows.Count = 1 Then
            ReDim arr(1 To 1, 1 To 1)
            arr(1, 1) = lo.DataBodyRange.Value
            lstBoxSuppliers.List = arr
        Else
            lstBoxSuppliers.List = lo.DataBodyRange.Value
        End If

 

[Akuini]

On which line it throws an error?

 

[JEC]

This will work

 On Error Resume Next
  If lo.DataBodyRange.Rows.Count = 1 Then
     'Do something
  Else
        lstBoxSuppliers.List = lo.DataBodyRange.Value
  End If
  On Error GoTo 0

 

[wsnyder]

On which line it throws an error?

Debugger points here. So I assume it is the initialization event of the form

Sub LoadSuppliersForm()
    frmSuppliers.Show vbModeless
End Sub

Here is the full code for the Initialization Event

Private Sub UserForm_Initialize()

    'Objects
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim lo As ListObject
        
    'Variables
        Dim arr As Variant
        
    'Initialize
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("dataSuppliers")
        Set lo = ws.ListObjects(1)
        
    'Populate listbox
        If lo.DataBodyRange.Rows.Count = 1 Then
            ReDim arr(1 To 1, 1 To 1)
            arr(1, 1) = lo.DataBodyRange.Value
            lstBoxSuppliers.List = arr
        Else
            lstBoxSuppliers.List = lo.DataBodyRange.Value
        End If
        
    'Tidy up
        Erase arr
        Set lo = Nothing
        Set ws = Nothing
        Set wb = Nothing
        
End Sub

 

[Fluff]

So I assume it is the initialization event of the form

In the Vb Editor click on Tools, Options, General & select "Break in Class Module", Ok.
That will enable you to see where the error is.

 

[wsnyder]

Thanks Fluff,

The problem was Erase arr in Cleanup
As soon as I commented it out, the form loaded correctly with 2 items in the Table.

Thanks,
-w

 

[Fluff]

Glad you sorted it & thanks for letting us know.