VBA userform Vlookup Excel

[jayeshsondhi]

Hi, I am making a userform in excel using VBA, I have a product code text box called'RentalDetails_TextBox' this is where the user should enter the product code of a game. I then have an empty label called "Label8' and I want the label to automaticaly do a vlookup for the game title when the user types in the product code. I have listed the current code I am using below but the error "Unable to get the Vlookup Property of the WorkSheetFunction Class" keeps apearing. "Stock" is the worksheet name where look up table is and "stocks" is cell array of the look up table. Please help

Private Sub RentalDetails_TextBox_Change()
 
If Len(RentalDetails_TextBox.Text) = 3 Then
    Label8.Caption = Application.WorksheetFunction. _
        VLookup(RentalDetails_TextBox.Text, Worksheets("Stock").Range("stocks"), 1, False)
    On Error GoTo 0
   
    If Ret <> "" Then MsgBox Ret
    End If
 
End Sub

 

[Artik]

For example:

Private Sub RentalDetails_TextBox_Change()
  Dim Ret  As Variant

  If Len(RentalDetails_TextBox.Text) = 3 Then
      On Error Resume Next
      Ret = Application.WorksheetFunction. _
      VLookup(RentalDetails_TextBox.Text, Worksheets("Stock").Range("stocks"), 1, False)
      On Error GoTo 0

      Label8.Caption = IIf(IsEmpty(Ret), "<Not found>", Ret)
  End If

End Sub

Artik