Im new to VBA and I
m 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