0

I have a userform that inputs data to Excel based on user input. Once the "Ok" command button is click the data is input and the form clears and moves to the next empty row. Before the data is input on the command click, I want to verify the text input with a pop up confirmation - "Employee ID #### is Bob Smith" - if this is correct users can select Ok and the data input code will continue, if not it will return to the userform to correct. I tried using a vlookup code but get a run time or stack overflow error.

Here is my code:

Private Sub OkButton1_Click()

Dim myLookupValue As String
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim myColumnIndex As Long
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myVLookupResult As Long

Dim myTableArray As Range

myLookupValue = IDTextBox
myFirstColumn = 1
myLastColumn = 2
myColumnIndex = 2
myFirstRow = 2
myLastRow = 500

    With Worksheets("Sheet2")
        Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
    End With

    myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)

    MsgBox "Employee Id" & myLookupValue & " is " & myVLookupResult
        Cancel = True

If Ok Then

Dim emptyRow As Long

'Make Sheet1 active
Sheet1.Activate

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
Dim x As Integer

    x = 0

    For Each cCont In Me.Controls
        If TypeName(cCont) = "CheckBox" Then
            If cCont.Value = True Then
                x = x + 1
            End If
        End If
    Next

    If x = 0 Then
        MsgBox "Please Select a Lunch Option"
        Else
        Cells(emptyRow, 1).Value = IDTextBox.Value
        End If

If MealCheckBox1.Value = True Then Cells(emptyRow, 2).Value = 5

If ExtraCheckBox2.Value = True Then Cells(emptyRow, 3).Value = 1

If DessertCheckBox3.Value = True Then Cells(emptyRow, 4).Value = 1

If DrinkCheckBox4.Value = True Then Cells(emptyRow, 5).Value = 1

Call Lunch_Initialize

IDTextBox.SetFocus

End If

End Sub

Using the code as is I get Run-Time error '1004' Unable to get the VLookup property of the WorksheetFunction class.

BigBen
  • 46,229
  • 7
  • 24
  • 40
clw_2020
  • 1
  • 1
    you need to handle the error when no match is found: https://stackoverflow.com/questions/45508472/vba-excel-iferror-vlookup-error – Scott Craner Feb 06 '20 at 17:20
  • Thanks Scott, the value I tested with was in my table so a match should have been found. – clw_2020 Feb 06 '20 at 17:44
  • It was not found in the first column of `myTableArray` hense the error. Check the data and make sure that the cells are truly equal and do not have a type mismatch, text <> number. or that there are not extra spaces. – Scott Craner Feb 06 '20 at 17:47

0 Answers0