Check this out Using drop down Val instead of input box (less typos)
I used a dynamic named range for column B
name is LookUpB
=OFFSET(Sheet2!$B$1,0,0,COUNTA(Sheet2!$B:$B))
then added a list data validation drop down in G1 (could go anywhere)
source =LookUpB (as we just named the range of names)
vlookup.jpg
Drop this on the Sheet2 code. it will run the lookup when ever you change the drop down value
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 _ ' is G1, just update if you move the drop down
And Target.Row = 1 Then
Call Vlookup
End If
End Sub
then alter the Vlookup macro to this
Sub Vlookup()
' Vlookup Macro
Dim LookUpValue As String
Dim Lookup As Variant
Dim LookUpWhat As String
LookUpWhat = Sheets(2).Range("G1") ' make this where ever you want the drop down to pick the name to look up
Lookup = Application.WorksheetFunction.Vlookup(LookUpWhat, Sheet2.Range("B2:C5"), 2, False)
MsgBox Lookup
'MsgBox "The number is " & Lookup
End Sub
The cool thing with this is as you add names and numbers on sheet2, the code won't need to be changed