0

Im new to VBA and Im trying to add a "search" command button to this "listform" but things are not like expected.Bellow what I tried and every single time I try to search something it`s comming back with a debug for SMTPTH_ListBox.Clear

Can someone help here please?

Thanks in advance

Private Sub CommandButton1_Click()

Dim rFilter As Range
Dim pc As String

pc = PNSearch_TextBox.Value


    SMTPTH_ListBox.Clear
    
    With Sheet1
        If .FilterMode Then .ShowAllData
        With .Range("A1:AI" & .Cells(.Rows.Count, "AI").End(xlUp).Row)
            .AutoFilter Field:=4, Criteria1:="=*" & pc & "*", Operator:=xlAnd
            On Error Resume Next
            Set rFilter = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rFilter Is Nothing Then
                Call UpdateListBox1(rFilter)
            End If
            '.AutoFilter
        End With
    End With
    
End Sub

Private Sub UpdateListBox1(ByVal rSourceRange As Range)

    Dim rRow As Range
    Dim i As Long
    Dim j As Long
    
    i = 0
    For Each rRow In rSourceRange.Rows
        With SMTPTH_ListBox
            .AddItem rRow.Cells(1, 1)
            For j = 1 To rRow.Cells.Count - 1
                .List(i, j) = rRow.Cells(1, j + 1).Value
            Next j
        End With
        i = i + 1
    Next rRow
    
End Sub

0 Answers0