asked on

Simple phonebook macro

I have Names in column A and telephone numbers in column B
I need a userform with two text boxes and one list box such that
when a user enters partial name in the first box and/or partial number in the second box the list box populates the records matching the two criterias. The entered texts could be found anywhere in the records for example

ali should match Malik, Ali or Wali

and 0300 should match 0300464365, 0423030023 or 3247890300

Saqib Husain

ASKER

Sample mock data typed in
PhonebookEE.xls

Robert Schutt

Here's a first stab at it:
PhonebookEE.xls

Saqib Husain

ASKER

Thanks so far. My comments

I cannot see how to run it without opening the VBA window
I do not need a "Search" button. I need it to update as I type
When both fields are entered I need only those records which match both fields

ASKER CERTIFIED SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

krishnakrkc

Hi

I haven't seen Robert's code. Anyway here is my take.

In Userform module.

Dim ka, Names(), Nums(), i As Long, r As Long
Private Sub TextBox1_Change()
    Dim q
    
    If Len(Me.TextBox1) Then
        q = Data(Me.TextBox1.Value, 1)
        Select Case r
            Case 1
                Me.ListBox1.Clear
                Me.ListBox1.AddItem q(1)
                Me.ListBox1.List(0, 1) = q(2)
            Case Is > 1
                Me.ListBox1.List = q
            Case Else
                Me.ListBox1.Clear
        End Select
    End If
        
End Sub

Private Sub TextBox2_Change()

    Dim q
    
    If Len(Me.TextBox2) Then
        q = Data(Me.TextBox2.Value, 2)
        Select Case r
            Case 1
                Me.ListBox1.Clear
                Me.ListBox1.AddItem q(1)
                Me.ListBox1.List(0, 1) = q(2)
            Case Is > 1
                Me.ListBox1.List = q
            Case Else
                Me.ListBox1.Clear
        End Select
    End If
    
End Sub

Private Sub UserForm_Initialize()
    
    ka = Range("a1").CurrentRegion.Resize(, 2).Value2
    
End Sub
Private Function Data(SearchKey As String, ColIdx As Long) As Variant
    Dim n As Long
    
    ReDim Names(1 To UBound(ka, 1))
    ReDim Nums(1 To UBound(ka, 1))
    r = 0
    For i = 2 To UBound(ka, 1)
        If InStr(1, CStr(ka(i, ColIdx)), SearchKey, 1) Then
            n = n + 1
            Names(n) = ka(i, 1)
            Nums(n) = ka(i, 2)
        End If
    Next
    If n Then
        ReDim Preserve Names(1 To n)
        ReDim Preserve Nums(1 To n)
        Data = Application.Transpose(Array(Names, Nums))
        r = n
    End If
    
End Function

Open in new window

Kris

Saqib Husain

ASKER

Perfect. Thanks.

Can I ask for an add on. I dont think it is worth asking a new question.

Can you add a command button
- which says "Add"
- which remains grey as long as some records are returned
- which is enabled if no data is displayed and both fields have data
- which adds this new data in the worksheet

Robert Schutt

Yeah sure. I just got back from a break, but will have a look when I get a chance.

Robert Schutt

Here's a new version:
PhonebookEEv3.xls

Saqib Husain

ASKER

Thanks. Just what I was looking for. Now I am getting newer ideas. Will be posting a new question. Wait for the link.

Saqib Husain

ASKER

Follow up question if you are around

https://www.experts-exchange.com/questions/28636136/Extending-a-simple-phonebook-application-in-excel-to-include-additional-columns.html