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
Bookmarks