Show information Issue

[JessyVal]

Hi Guys, I hope I can explain myself clearly . I have a user form, what I want to do is:

Type a name, (let's think that I have 3 people call Jessica but with different last names), when I click in search, I want to be able to see displayed the 3 names Jessica inside the list box
The sheet name where I have all the data is called "Registers"

With the code that I have now, just shows "can't find register", but I don't know what to do to solve it

something similar to this:

This is the piece of code that I have in vba

Private Sub CommandButton5_Click()
On Error GoTo Errores
If Me.txtFiltro1.Value = "" Then Exit Sub
Me.ListBox1.Clear
j = 1
Filas = Worksheets("Registers").Select.CurrentRegion.Rows.Count
For i = 2 To Filas
    If LCase(Cells(i, j).Offset(0, 2).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then
        Me.ListBox1.AddItem Cells(i, j)
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = Cells(i, j).Offset(0, 1)
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = Cells(i, j).Offset(0, 2)
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = Cells(i, j).Offset(0, 3)
    Else
    End If
Next i
Exit Sub
Errores:
MsgBox "Can't find register.", vbExclamation, "Error"
End Sub


Private Sub ListBox1_Click()
Worksheets("Registers").Select
Cuenta = Me.ListBox1.ListCount
Set Rango = Worksheets("Registers").Select
For i = 0 To Cuenta - 1
    If Me.ListBox1.Selected(i) Then
        Valor = Me.ListBox1.List(i)
        Rango.Find(What:=Valor, LookAt:=xlWhole, After:=ActiveCell).Activate
    End If
Next i
End Sub


Private Sub UserForm_Initialize()
For i = 1 To 3
    Me.Controls("Label" & i) = Cells(1, i).Value
Next i

With ListBox1
    .ColumnCount = 3
    .ColumnWidths = "60 pt;60 pt;60 pt"
End With
End Sub

I was following a tutorial, but I really don't understan how this person link the table with the buttons (this is the code that I was following)

Private Sub CommandButton5_Click()
On Error GoTo Errores
If Me.txtFiltro1.Value = "" Then Exit Sub
Me.ListBox1.Clear
j = 1
Filas = Range("a1").CurrentRegion.Rows.Count
For i = 2 To Filas
    If LCase(Cells(i, j).Offset(0, 2).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then
        Me.ListBox1.AddItem Cells(i, j)
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = Cells(i, j).Offset(0, 1)
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = Cells(i, j).Offset(0, 2)
        Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = Cells(i, j).Offset(0, 3)
    Else
    End If
Next i
Exit Sub
Errores:
MsgBox "No se encuentra.", vbExclamation, "EXCELeINFO"
End Sub
'
'Activar la celda del registro elegido
Private Sub ListBox1_Click()
Range("a2").Activate
Cuenta = Me.ListBox1.ListCount
Set Rango = Range("A1").CurrentRegion
For i = 0 To Cuenta - 1
    If Me.ListBox1.Selected(i) Then
        Valor = Me.ListBox1.List(i)
        Rango.Find(What:=Valor, LookAt:=xlWhole, After:=ActiveCell).Activate
    End If
Next i
End Sub
'
'Dar formato al ListBox y traer datos de la tabla
Private Sub UserForm_Initialize()
For i = 1 To 4
    Me.Controls("Label" & i) = Cells(1, i).Value
Next i

With ListBox1
    .ColumnCount = 4
    .ColumnWidths = "60 pt;60 pt;60 pt;60 pt"
End With
End Sub

Thank you so much in advance

 

[JessyVal]

no, What I want to look for is the column 2, where should i change that?

Now that you said about column 3, I search a data from that column and is working!

 

[Fluff]

Ok, try it like

With Worksheets("Registers")
   filas = .Range("A1").CurrentRegion.Rows.Count
   MsgBox filas
   For i = 2 To filas
       If LCase(.Cells(i, 2).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then
           Me.ListBox1.AddItem .Cells(i, 2)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 1) = .Cells(i, 2).Offset(0, 1)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 2) = .Cells(i, 2).Offset(0, 2)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 3) = .Cells(i, 2).Offset(0, 3)
       End If
   Next i
End With

 

[JessyVal]

If I do it like that the information that i received is the column 2, 3 and 4 of registers

 

[Fluff]

Which columns do you want in the listbox?

 

[JessyVal]

If I do it like that the information that i received is the column 2, 3 and 4 of registers

Which columns do you want in the listbox?

Which columns do you want in the listbox?

I want to visualize column A, B and C

But if i change 1 instead 2, works perfect

If LCase(.Cells(i, j).Offset(0, 1).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then..

so, lets say that if i leave this code works well

Private Sub CommandButton5_Click()

If Me.txtFiltro1.Value = "" Then Exit Sub
Me.ListBox1.Clear
j = 1
With Worksheets("Registers")
Filas = .Range("A1").CurrentRegion.Rows.Count
For i = 2 To Filas
If LCase(.Cells(i, j).Offset(0, 1).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then
Me.ListBox1.AddItem .Cells(i, j)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = .Cells(i, j).Offset(0, 1)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = .Cells(i, j).Offset(0, 2)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = .Cells(i, j).Offset(0, 3)
Else
End If

Next i

End With

Exit Sub

 

[Fluff]

Do you want col D as well?

 

[JessyVal]

Do you want col D as well?

sorry, no, I nedd column a b and c

if I use this code

If Me.txtFiltro1.Value = "" Then Exit Sub
Me.ListBox1.Clear
j = 1
With Worksheets("Registers")
Filas = .Range("A1").CurrentRegion.Rows.Count
For i = 2 To Filas
If LCase(.Cells(i, j).Offset(0, 1).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then
Me.ListBox1.AddItem .Cells(i, j)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = .Cells(i, j).Offset(0, 1)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = .Cells(i, j).Offset(0, 2)
Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = .Cells(i, j).Offset(0, 3)
Else
End If

Next i

End With

Exit Sub

the program works well

 

[Fluff]

In that case try

With Worksheets("Registers")
   filas = .Range("A1").CurrentRegion.Rows.Count
   For i = 2 To filas
       If LCase(.Cells(i, 2).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then
           Me.ListBox1.AddItem .Cells(i, 1)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 1) = .Cells(i, 1).Offset(0, 1)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 2) = .Cells(i, 1).Offset(0, 2)
       End If
   Next i
End With

 

[JessyVal]

In that case try

With Worksheets("Registers")
   filas = .Range("A1").CurrentRegion.Rows.Count
   For i = 2 To filas
       If LCase(.Cells(i, 2).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then
           Me.ListBox1.AddItem .Cells(i, 1)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 1) = .Cells(i, 1).Offset(0, 1)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 2) = .Cells(i, 1).Offset(0, 2)
       End If
   Next i
End With

that code workd perfectly! thanks a lot, Just one more think, if it is possible, if no it is ok, i already took lots of your time. How can I add a box message in case the register doesnt exist?

 

[Fluff]

How about

With Worksheets("Registers")
   filas = .Range("A1").CurrentRegion.Rows.Count
   For i = 2 To filas
       If LCase(.Cells(i, 2).Value) Like "*" & LCase(Me.txtFiltro1.Value) & "*" Then
           j = j + 1
           Me.ListBox1.AddItem .Cells(i, 1)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 1) = .Cells(i, 1).Offset(0, 1)
           Me.ListBox1.list(Me.ListBox1.ListCount - 1, 2) = .Cells(i, 1).Offset(0, 2)
       End If
   Next i
   If j = 0 Then MsgBox "Nothing found"
End With