Using VLookup in VBA

[simoscarl]

I have a form with a combo box, ComboBox1, that allows the user to select an existing employee from the range DATATBL ("a2500") 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.

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

 

[Bob Phillips]

See if this is any better

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

 

[simoscarl]

Thank you Bob. This worked perfectly.

See if this is any better

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