Error when empty listbox

问题:

Im having two issues with the code below; it works just fine until there is nothing to show on the listbox, the I get an error message; and the second is I cant get It the refresh the data on the listbox.

Heres what Ive got.

Private Sub UserForm_Initialize()

Dim tiempoa As Date
Dim tiempob As Date
Dim tiempoc As Date

Dim seguimiento As Long, i As Long
Dim Data() As Variant 
Dim cell As Range


With Me.ListBox1
    .ColumnCount = 6
    .ColumnWidths = "40;82;117;117;60;180"
End With

i = 1
With Hoja2 
    With .Range("T2:T" & .Cells(.Rows.Count,"B").End(xlUp).Row).SpecialCells(xlCellTypeConstants) 
        seguimiento = .Count 
        ReDim Data(1 To seguimiento + 1, 1 To Me.ListBox1.ColumnCount) 

        Data(1, 1) = "FOLIO"
        Data(1, 2) = "NOMBRE"
        Data(1, 3) = "APELLIDO PATERNO"
        Data(1, 4) = "APELLIDO MATERNO"
        Data(1, 5) = "HORAS"
        Data(1, 6) = "DIAGNOSTICO DE TRIAGE"

        For Each cell In .Cells
            i = i + 1
            With cell
                Data(i, 1) = .Offset(, -19) 
                Data(i, 2) = .Offset(, -17) 
                Data(i, 3) = .Offset(, -16) 
                Data(i, 4) = .Offset(, -15) 
                            tiempoa = .Offset(, -18).Value
                            tiempob = Now
                            tiempoc = Format(tiempoa - tiempob, "hh:mm")
                Data(i, 5) = tiempoc 
                Data(i, 6) = .Offset(, -3) 
            End With
        Next cell
    End With
End With

ListBox1.List = Data 
End Sub

回答1:

place a count check on the range you want to fill listbox with, like follows:

Option Explicit

Private Sub UserForm_Initialize()

    Dim tiempoa As Date
    Dim tiempob As Date
    Dim tiempoc As Date

    Dim seguimiento As Long, i As Long
    Dim Data() As Variant
    Dim cell As Range


    With Me.ListBox1
        .ColumnCount = 6
        .ColumnWidths = "40;82;117;117;60;180"
    End With

    i = 1
    With Hoja2
        With .Range("T2:T" & .Cells(.Rows.Count, "B").End(xlUp).row)

            If WorksheetFunction.Count(.Cells) = 0 Then Exit Sub '<--| if no cells in referenced range then exit sub

            With .SpecialCells(xlCellTypeConstants)
                seguimiento = .Count
                ReDim Data(1 To seguimiento + 1, 1 To Me.ListBox1.ColumnCount)

                Data(1, 1) = "FOLIO"
                Data(1, 2) = "NOMBRE"
                Data(1, 3) = "APELLIDO PATERNO"
                Data(1, 4) = "APELLIDO MATERNO"
                Data(1, 5) = "HORAS"
                Data(1, 6) = "DIAGNOSTICO DE TRIAGE"

                For Each cell In .Cells
                    i = i + 1
                    With cell
                        Data(i, 1) = .Offset(, -19)
                        Data(i, 2) = .Offset(, -17)
                        Data(i, 3) = .Offset(, -16)
                        Data(i, 4) = .Offset(, -15)
                                    tiempoa = .Offset(, -18).value
                                    tiempob = Now
                                    tiempoc = Format(tiempoa - tiempob, "hh:mm")
                        Data(i, 5) = tiempoc
                        Data(i, 6) = .Offset(, -3)
                    End With
                Next cell
            End With
        End With
    End With

    Me.ListBox1.List = Data

End Sub