Using VLookup in VBA

simoscarl

New member
Joined
May 30, 2014
Messages
2
Reaction score
0
Points
0
I have a form with a combo box, ComboBox1, that allows the user to select an existing employee from the range DATATBL ("a2:D500") on the worksheet HideEmployee. I then want to use that value to look up several other pieces of data from that employee record and display in the form. I have tried several code examples but keep coming up with error 424 Object required. I a looking for the proper way address the range in VLOOKUP function. Thank you for your help.

Code:
Private Sub ComboBox1_change()
     Dim cStatus as string, cClass as string, cEmp as string
     cEmp=Me!ComboBox1.Value
     ' cStatus=Application.Vlookup(cEmp, Sheet.hideemployee.range("a2:d500"), 4, False)
     cStatus=Excel.Application.WorksheetFunction.Vlookup(cEmp,Sheet.HideEmployee.Range("a2:d500"), 4, False)
     if cStatus="A" then
          Me!CheckBox1.Value=True
     else
          Me!CheckBox1.Value=False
     end if
End Sub
 
Last edited by a moderator:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
See if this is any better

Code:
Private Sub ComboBox1_change()
Dim cStatus As String, cClass As String, cEmp As String
    cEmp = Me.ComboBox1.Value
    cStatus = Application.VLookup(cEmp, Sheets("HideEmployee").Range("A2:D500"), 4, False)
    Me.CheckBox1.Value = cStatus = "A"
End Sub
 
Last edited:

simoscarl

New member
Joined
May 30, 2014
Messages
2
Reaction score
0
Points
0
Thank you Bob. This worked perfectly.

See if this is any better

Code:
Private Sub ComboBox1_change()Dim cStatus As String, cClass As String, cEmp As String
    cEmp = Me.ComboBox1.Value
    cStatus = Application.VLookup(cEmp, Sheets("HideEmployee").Range("A2:D500"), 4, False)
    Me.CheckBox1.Value = cStatus = "A"
End Sub
 
Top