• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search And Filter Cases


New Member
Good afternoon friends, I hope you are well.
I am trying to modify the RPAT form (frmrpat) to suit my needs however I cannot resolve the following details:

1. Adapt the following macro from a YouTube tutorial

>>> use code - tags <<<
Private Sub CmdBUSCAR_Click()

Dim UFILA As Integer

If Me.TextBox11 = "" Then
MsgBox "Ingrese el Número de Cédula", vbExclamation
End If
Exit Sub

ROW = Sheets("MATRIZ").Range("B" & Rows.Count).End(xlUp).Row

Me.ListBox1.RowSource = Clear
For Row = 2 To UFILA

If UCase(Range("B" & Row).Value) Like "*" & UCase(Me.TextBox11.Value) & "*" Then

Me.ListBox1.List(ListBox1.ListCount - 1, 0) = Cells(Row, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 19) = Cells(Row, 21)
Me.ListBox1.List(ListBox1.ListCount - 1, 23) = Cells(Row, 25)
Me.ListBox1.List(ListBox1.ListCount - 1, 24) = Cells(Row, 26)
Me.ListBox1.List(ListBox1.ListCount - 1, 45) = Cells(Row, 47)
End If

Next Row

Me.TextBox11.Value = Empty

End Sub

to search for the cases by the ID number and show the results in ListBox1, however it asks me to declare the variables Clear and Row and when doing so it does not carry out any kind of search and in ListBox1 all the cases present in the list continue to appear. Sheet3 (MATRIZ).

2. Depending on the case (AT or EO) that has been selected in Combobox1, ListBox1 should filter and reflect the number of cases associated with the identification number searched in TextBox11 when in Sheet3 (MATRIZ) AU=”” and AU <>””


  • PRUEBA.xlsm
    161.4 KB · Views: 6
Last edited by a moderator:
Hi, some codelines useless or no sense … As you forgot to allocate the variable UFILA so nothing is done !​
(And before the very misplaced Exit Sub !)
An easier way according to VBA help and just using the Macro Recorder :​
allocate the data range to RowSource and filter the range according to the UserForm query.​
Greetings my friend, I hope you are well, thank you for answering my concern. I don't know how to do what you tell me. I have little knowledge of Vba. If you could help me by making the corrections in the attached file, I would be very grateful.
As it works only with smart ranges so according to your attachment :​
first delete the last row - as empty - of the source data table,​
the LISTAS worksheet from column F is used for the filtered data with the below sample.​
To see the headers then you just have to set the ListBox ColumnHeads property to True.​
Set also the ColumnCount property to 5 according to the below sample.​
My two cents : TextBox11 should be more user friendly as a ComboBox …
For starters replace all the UserForm module with this Excel basics table filter sample :​
Private Sub CmdBUSCAR_Click()
        ListBox1.RowSource = ""
   With Hoja3.ListObjects(1).Range
        If TextBox11 = "" Then .AutoFilter 1 Else .AutoFilter 1, TextBox11
        If ComboBox1.ListIndex < 1 Then .AutoFilter 46 Else .AutoFilter 46, Array("=", "<>")(ComboBox1.ListIndex - 1)
       .Range(Replace("A1:A#,T1:T#,X1:Y#,AT1:AT#", "#", .Rows.Count)).Copy Hoja1.[F1]
   End With
        ListBox1.RowSource = Hoja1.[F1].CurrentRegion.Offset(1).Address(, , , 1)
End Sub

Private Sub ListBox1_Click()
    TextBox11 = ListBox1
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = Hoja1.[B2].CurrentRegion.Address(, , , 1)
End Sub

Private Sub UserForm_Terminate()
End Sub
Do you like it ? So thanks to click on bottom right Like !​