Look up value from sheet into VBA form

[selant]

Hi there,

I will start a project which sends given pre-defined text to the selected receiptients by their email adress. For example, I have a sheet (Sheet : WORKERS) containing the following data :

ID - Name - Email
-------------------
001 Tom tom@xxx.com
002 Bryan bryan@xxx.com
003 Jason jason@yyy.com
004 Lynda lynda@zzz.com
005 Mike mike@mmm.com
...

What i will do is, to create a VBA form and enter ID data to the textbox, and what I want to know is if its possible to lookup the receptient email adress from the 3rd column if the given ID data finds the match through the rows in WORKERS sheet. I am working in a company where each staff has unique ID number. I will be very happy if somebody can show me how to look up the value, and fetch the corresponding email address to another textbox in the form. It is very important for me.

Thank you very much in advance

 

[MickG]

Hi, Create a Userform in you Data sheet, With "TextBox1" and "TextBox2" on it.
Place this code in the Change event for TextBox1 (TextBox1 to enter ID Number in.) as shown.

Private [COLOR="Navy"]Sub[/COLOR] TextBox1_Change()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] TextBox2
        .WordWrap = True
        .MultiLine = True
     [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = Val(TextBox1.Value) [COLOR="Navy"]Then[/COLOR]
        TextBox2.Value = Dn.Next & "'[COLOR="Green"][B]s Email address is :- " & Dn.Next.Next[/B][/COLOR]
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[selant]

Thanks mick, i am trying it right now..

 

[selant]

Hi Mick, how can i modify this code to use it in multiple worksheet file. How to mention about the sheet name, or changing the active sheet to look up ?

 

[Norie]

Create a userform with a combobox and 2 textboxes.

Add this code.

Option Explicit
 
Private Sub ComboBox1_Change()
    With ComboBox1
        If .ListIndex <> -1 Then
            TextBox1.Value = Range("B" & .ListIndex + 2)
            TextBox2.Value = Range("C" & .ListIndex + 2)
        End If
    End With
End Sub
Private Sub UserForm_Initialize()
Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    ComboBox1.RowSource = "A2:A" & LastRow - 1
    
End Sub

 

[MickG]

Hi, What exactly do you want to do.!! Do you want to enter a Number and search for "Email" address in Multiple sheets, if so is that all sheets or specific sheets.
Mick

 

[selant]

Hi, What exactly do you want to do.!! Do you want to enter a Number and search for "Email" address in Multiple sheets, if so is that all sheets or specific sheets.
Mick

Hi Mick, the code you gave me works, this is what i want to do but, there will be multiple sheets in my file.. So i want to make the look up in the sheet named "WORKERS", i dont want to search the number in all sheets, i just need to make the search in "WORKERS" sheet. Thanks a lot

 

[MickG]

Hi, Try this:-

Private [COLOR="Navy"]Sub[/COLOR] TextBox1_Change()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("WORKERS")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] TextBox2
        .WordWrap = True
        .MultiLine = True
     [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = Val(TextBox1.Value) [COLOR="Navy"]Then[/COLOR]
        TextBox2.Value = Dn.Next & "'[COLOR="Green"][B]s Email address is :- " & Dn.Next.Next[/B][/COLOR]
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[selant]

This is exactly what i wanted, thank you very much. Norie, thank you very much too.