JessyVal

New Member
Joined
Feb 23, 2021
Messages
29
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
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
 

JessyVal

New Member
Joined
Feb 23, 2021
Messages
29
Office Version
  1. 365
Hi!

Thanks for your answer!

Still is not showing any information.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Remove this line
VBA Code:
On Error GoTo Errores
& then try again, what happens?
 

JessyVal

New Member
Joined
Feb 23, 2021
Messages
29
Office Version
  1. 365

ADVERTISEMENT

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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
oops missed that, it should be between the Next & Exit Sub lines.
 

JessyVal

New Member
Joined
Feb 23, 2021
Messages
29
Office Version
  1. 365

ADVERTISEMENT

oops missed that, it should be between the Next & Exit Sub lines.
I put it in the righ place now, but still no changes
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Do you get any error messages?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Are you trying to search Col C & is it text?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,801
Messages
5,638,437
Members
417,025
Latest member
MusterDuster

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
Top