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:

Please Login or Register  to view this content.