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:

JessyVal

New Member
Joined
Feb 23, 2021
Messages
29
Office Version
  1. 365
How about
VBA Code:
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
Works Perfect,
Thank you so much for all your help!!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

JessyVal

New Member
Joined
Feb 23, 2021
Messages
29
Office Version
  1. 365
Sorry, there is another question 😖.

Once I select a name, what I want to do it is to show another form that allows the user to modify records. Let me try to explain

I have a form called userform1, that form is to show information about people , I can find them by id or by name, I can modify information from that form. Once you click in the search buttom, it is going to open the 2nd userform (the one that you helped me to do)
1618831669356.png


Then, I have a second form (the one that you helped me), this second form was created because in case the manager is not sure about the id, or there are many people with the same name , the manager can find easily the person that he is looking for by clicking search and are going to appear all the possible people, and the manager can click in the person that he wants and edit it.

1618831758291.png


The point now, is that in that second form, when I click in some name and I wanto to modify it (open the first form), I get an error

1618831975674.png


this is the code that i have in the buttom Modificar (modify)

Private Sub CommandButton3_Click()
If Me.ListBox1.ListIndex < 0 Then
MsgBox "You haven't selected any record"
Else
UserForm1.Show
End If

End Sub

I hope I was able to explain myself
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
As this is a totally different question, you need to start a new thread. Thanks
 

Forum statistics

Threads
1,148,173
Messages
5,745,179
Members
423,931
Latest member
thangvan114

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