Show information Issue

JessyVal

New Member
Joined
Feb 23, 2021
Messages
30
Office Version
  1. 365
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

1618592364676.png


something similar to this:
1618592473685.png


This is the piece of code that I have in vba

VBA Code:
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)

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Try
VBA Code:
Private Sub CommandButton5_Click()
On Error GoTo Errores
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, 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 = 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


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
 
Upvote 0
Remove this line
VBA Code:
On Error GoTo Errores
& then try again, what happens?
 
Upvote 0
I deleted that line and Nothing happens.

I have one question, to close the first with (the one that is in commandButton5, it is just before End Sub?
 
Upvote 0
oops missed that, it should be between the Next & Exit Sub lines.
 
Upvote 0
Do you get any error messages?
 
Upvote 0
Are you trying to search Col C & is it text?
 
Upvote 0

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top