Prev: Screen repainting issue
Next: Referencing Defined Name in another workbook
From: Andrew on 5 May 2010 11:45
Hello,
I have a user form with textbox1 and textbox2. I want to enter a
phone number into textbox1, and then I want textbox2 to be populated
from another sheet based on the value in textbox1, just like a lookup
table. So the name of the person whose phone number is in textbox1
will appear in textbox2. Does anyone know how to do this?

thanks
From: Dave Peterson on 5 May 2010 13:03
I created a small userform -- two textboxes and a commandbutton.

This was the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()

Dim res As Variant
Dim LookUpRng As Range

If Me.TextBox1.Value = "" Then
Beep
Exit Sub
End If

Set LookUpRng = ThisWorkbook.Worksheets("Sheet1").Range("A:B")

'look for a text match 123-124-1235
res = Application.VLookup(Me.TextBox1.Value, LookUpRng, 2, False)
If IsError(res) Then
If IsNumeric(Me.TextBox1.Value) Then
'look for a number match
res _
= Application.VLookup(Val(Me.TextBox1.Value), LookUpRng, 2, False)
End If
End If

If IsError(res) Then
'not found either way
res = "No Match!"
End If

Me.TextBox2.Value = res

End Sub


Andrew wrote:
>
> Hello,
> I have a user form with textbox1 and textbox2. I want to enter a
> phone number into textbox1, and then I want textbox2 to be populated
> from another sheet based on the value in textbox1, just like a lookup
> table. So the name of the person whose phone number is in textbox1
> will appear in textbox2. Does anyone know how to do this?
>
> thanks

--

Dave Peterson
From: Andrew on 5 May 2010 13:15
On May 5, 11:03 am, Dave Peterson <peter...(a)verizonXSPAM.net> wrote:
> I created a small userform -- two textboxes and a commandbutton.
>
> This was the code behind the userform:
>
> Option Explicit
> Private Sub CommandButton1_Click()
>
>     Dim res As Variant
>     Dim LookUpRng As Range
>
>     If Me.TextBox1.Value = "" Then
>         Beep
>         Exit Sub
>     End If
>
>     Set LookUpRng = ThisWorkbook.Worksheets("Sheet1").Range("A:B")
>
>     'look for a text match 123-124-1235
>     res = Application.VLookup(Me.TextBox1.Value, LookUpRng, 2, False)
>     If IsError(res) Then
>         If IsNumeric(Me.TextBox1.Value) Then
>            'look for a number match
>             res _
>              = Application.VLookup(Val(Me.TextBox1.Value), LookUpRng, 2, False)
>         End If
>     End If
>
>     If IsError(res) Then
>         'not found either way
>         res = "No Match!"
>     End If    
>
>     Me.TextBox2.Value = res
>
> End Sub
>
> Andrew wrote:
>
> > Hello,
> > I have a user form with textbox1 and textbox2.  I want to enter a
> > phone number into textbox1, and then I want textbox2 to be populated
> > from another sheet based on the value in textbox1, just like a lookup
> > table.  So the name of the person whose phone number is in textbox1
> > will appear in textbox2.  Does anyone know how to do this?
>
> > thanks
>
> --
>
> Dave Peterson

Thank you. This is exactly what I was looking for.
 | 
Pages: 1
Prev: Screen repainting issue
Next: Referencing Defined Name in another workbook