VBA Code
Private Sub TextBox1_Change()
On Error Resume Next
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Me.ListBox1.Clear
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 4
a = Len(Me.TextBox1.Text)
If Left(Sheet1.Cells(i, x).Value, a) = Me.TextBox1.Text And Me.TextBox1.Text <> "" Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For c = 1 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
Next c
End If
Next x
Next i
End Sub
New Code With Capture Column Heds Data
Private Sub UserForm_Initialize()
Me.TextBox1.SetFocus
End Sub
Private Sub TextBox1_Change()
On Error Resume Next
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Me.ListBox1.Clear
Me.ListBox1.AddItem Sheet1.Cells(1, "A")
For B = 2 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, B - 1) = Sheet1.Cells(1, B)
Next B
Me.ListBox1.Selected(0) = True
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 4
a = Len(Me.TextBox1.Text)
If Left(Sheet1.Cells(i, x).Value, a) = Me.TextBox1.Text And Me.TextBox1.Text <> "" Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For c = 1 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
Next c
End If
Next x
Next i
End Sub
On Error Resume Next
Me.TextBox1.Text = StrConv(Me.TextBox1.Text, vbProperCase)
Me.ListBox1.Clear
Me.ListBox1.AddItem Sheet1.Cells(1, "A")
For B = 2 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, B - 1) = Sheet1.Cells(1, B)
Next B
Me.ListBox1.Selected(0) = True
Dim i As Long
For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))
For x = 1 To 4
a = Len(Me.TextBox1.Text)
If Left(Sheet1.Cells(i, x).Value, a) = Me.TextBox1.Text And Me.TextBox1.Text <> "" Then
Me.ListBox1.AddItem Sheet1.Cells(i, 1).Value
For c = 1 To 4
Me.ListBox1.List(ListBox1.ListCount - 1, c) = Sheet1.Cells(i, c + 1).Value
Next c
End If
Next x
Next i
End Sub
download .xlsm
ReplyDeletemuito bom
DeleteNice
ReplyDeleteI have copied the above code into a few spreadsheets and only the headers load into to the list box. The data from the cells below does not populate. Any ideas?
ReplyDeletegreat code but im getting a error message saying that variable x is not defined. any idea how to fix?
ReplyDeletescratch that... i fixed it!
DeleteHi thanks for the VBA. But after I copied your code I found a bug in it. If I search the last row, it won't appear, it only scan from the begining to the before last row. So the code "For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))" should be "For i = 2 To Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))+1"
ReplyDeleteThanks for your code again.
i also so, thank you so much.
DeleteUr listbox is not working what should
ReplyDeleteI do
i also so, thank you so much
ReplyDeleteThis written piece gives fastidious understanding yet.
ReplyDeletewww.telephonecodes.org
Please, you had an example of how to use an invoice database, how to extract the invoices of a client that are pending payment, I have an example but I do not really understand you,I'm from Mexico and I'm trying to learn excel vba.
ReplyDeleteFirst of all, Thanks
These are actually wonderful some ideas in the blog. You have touched good quality points here. In whatever way continue writing.
ReplyDeletehttp://www.telephonecodes.org
This short article posted only at the web site is truly good.
ReplyDeletehttp://www.telephonecodes.org
While the admin of the web site is working, no question soon it will likely be famous, due to its feature blogs.
ReplyDeletetelephonecodes
This is my first visit to your web journal! We are a group of volunteers and new activities in the same specialty. Website gave us helpful data to work. Localisertel.com
ReplyDelete