Thursday, February 16, 2017

Searching Telephone Directory Excell VBA



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

16 comments:

  1. download .xlsm

    ReplyDelete
  2. I 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?

    ReplyDelete
  3. great code but im getting a error message saying that variable x is not defined. any idea how to fix?

    ReplyDelete
    Replies
    1. scratch that... i fixed it!

      Delete
  4. Hi 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"
    Thanks for your code again.

    ReplyDelete
  5. Ur listbox is not working what should
    I do

    ReplyDelete
  6. i also so, thank you so much

    ReplyDelete
  7. This written piece gives fastidious understanding yet.
    www.telephonecodes.org

    ReplyDelete
  8. 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.
    First of all, Thanks

    ReplyDelete
  9. These are actually wonderful some ideas in the blog. You have touched good quality points here. In whatever way continue writing.
    http://www.telephonecodes.org

    ReplyDelete
  10. This short article posted only at the web site is truly good.
    http://www.telephonecodes.org

    ReplyDelete
  11. While the admin of the web site is working, no question soon it will likely be famous, due to its feature blogs.
    telephonecodes

    ReplyDelete
  12. 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

Please do not enter any spam message in comment box