I am working on making a data input form in excel which requires that the user uses a barcode scanner to scan in a string value. The string value is something like "H2-0030$821E". The portion before the dollar sign is the lot number of a process input. The portion after the dollar sign is a checksum of the lot number. The purpose of the checksum is to make sure that the user scans the number instead of typing it in.

I don't want the user to see the checksum so it needs to be changed upon being scanned in or processed/validated and then changed leaving only the lot_number in the cell where the user scanned the barcode into. The problem is that if I set up data validation on a cell, I cannot change the value of that cell in my function that returns a boolean. The code I have posted validates properly however I cannot change the value of "source_lot" while validating it. I guess I don't understand the order of events when data validation triggers.

I have two single cell ranges: worksheet("runsheet").range("source_lot") (the cell where the barcode string goes) and worksheet("validation").range("source_lot_validator"). Range("source_lot") has custom data validation pointing to "source_lot_validator" for a boolean value. "source_lot_validator" is set to the custom function "compare_source_lot" which is below:

Private Function encode_source_checksum(source As String)
    ' If source is "H2-0030" then
    Dim alphabet As String
    Dim source_type As String       ' "H2"
    Dim type_number As String       ' "2"
    Dim source_serial As String     ' "0030"
    Dim srcArray() As String        ' put the split strings here
    Dim letter_number As Integer    ' 8 for H, the 8th letter
    Dim letter_code As String       ' hex(letter_number)
    Dim number_code As String       ' hex(type_number)
    Dim serial_code As String       ' hex(source_serial)
    Dim i As Integer                ' iterator for going over the alphabet
    Dim cs As String                ' the checksum value

    On Error GoTo ErrorHandler

    letter_code = ""
    alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    srcArray = Split(source, "-")
    source_type = srcArray(0)       'H2
    source_serial = srcArray(1)     '0030

    i = 0
    Do While letter_code <> Left(source_type, 1)
        i = i + 1
        letter_code = Mid(alphabet, i, 1)
    Loop
    letter_number = i

    type_number = Right(source_type, Len(source_type) - 1) ' 2

    letter_code = Hex(CInt(letter_number))
    number_code = Hex(CInt(type_number))
    serial_code = Hex(CInt(source_serial))
    cs = letter_code & number_code & serial_code
    ' MsgBox (cs)
    encode_source_checksum = cs
    Exit Function

ErrorHandler:
    encode_source_checksum = False

End Function

Public Function compare_source_lot(src As String)
    ' src is something like "H2-0030$821E" which is a correct value
    ' if src were "H2-0030$821H", that would be incorrect and
    ' the validation should fail and tell the user so.
    Dim source_lot As String    ' H2-0030
    Dim checksum As String      ' 821E
    Dim strArray() As String    ' array to split src into
    Dim src_rng As Range        ' the range needed to change after
                                ' of before validation is performed.

    On Error GoTo ErrorHandler
    Application.EnableEvents = False

    ' Split up the input string based on the $ delimeter
    strArray = Split(src, "$")
    If UBound(strArray) + 1 = 2 Then
        source_lot = strArray(0)
        checksum = strArray(1)
    Else
        ' If no "$" in the cell, then return false because it
        ' wasn't scanned in.
        compare_source_lot = False
    End If

    ' MsgBox (source_lot & " " & checksum)

    ' calculate the checksum from the source_lot input string
    ' and see if it is equal to the passed checksum
    If checksum = encode_source_checksum(source_lot) Then
        compare_source_lot = True
    Else
        compare_source_lot = False
    End If

ErrorHandler:
    Set src_rng = Worksheets("CG_SOB_Runsheet").Range("source_lot")
'    src_range.Value = source_lot
    Application.EnableEvents = True

End Function