Using Barcode and Data Validation List to Prevent Errors

[2KGrafix]

So I set up a data validation list. I have a VBA code written (Thanks Pete) that converts the original barcode info into the correct part number by searching for specific patterns. However, I noticed I can scan an incorrect barcode (part number) into the text field where my data validation is, and NO errors occur. So column B is where I place my cursor before scanning, column C has the =barcode(A2) formula to reference the VBA for the pattern, and column D has the formula =C2 to pull the readout text from the output of the pattern. For some reason when I deliberately scan the wrong part into column B, it reads out a part that is not in my data validation list in column C. Does anyone know why this happens?

B	C	D
Scan	=barcode(A1)	PCLB5B13W020CG

 

[PeteWright]

Can you make a screenshot of the affected cells? (formulas, data validation, etc.)

 

[2KGrafix]

Can you make a screenshot of the affected cells? (formulas, data validation, etc.)

Cell "D" is the affected cell, which is also labeled component.

Cell B14 Scan: RB5B13W020CG RH SAE HIGH U625(2) 12/05/23 16:36:55
Cell C14 formula: =barcode(B14) converts based on the pattern code you wrote
Cell D14 (Component) formula: =C14
Cell D14 also contains the data validation list which references an array on a separate sheet.

Let's take the example below. That part number is incorrect. It does NOT exist in the data validation list. However, when I scan that barcode into cell B14, it will appear in cell D14(Component). I was thinking it would give an error just like if a user were to type the information in and it was not in the data validation list dropdown. What do you think? Is there a workaround to this?

 

[PeteWright]

I tried to reproduce it...

So I made a sheet, "Sheet4" with some of your sample Part numbers

MrExcel_2KGrafix_barcode_scan.xlsm
	A
1	PCL1MA19A286AC
2	PCL1MH19F667AB
3	PCL1M39C675AC
4	PCLC5B13E015CN
5	PCP1MT14A303BAUB
6	PCRC5B13E014AE
7	PCRB5B13W029CG
8	PCM1M39E635BA
9	PCL1M39C675AC
10	PCL1M38005AF
11	PCL1M36K775BF
12	PCP1M38C607GA
13	PCLC5B13E014AR
14	PCM1M38W005AAA
15	PCL1M38B274BAH
16	PCMB5B13W029BF
17	PCRC5B17K945AA59B8
18	PCRB5B17F771BA5UAW
19	PCRB5B17F771BAFLAD
20	MLDRB5B17F775AB
21	PCRB5B17C831AB51MD
22	PCLB5B17F001AG53CC
23	PCRC5B17C831BC5KWH
24	PCRB5B8200DBSMA4
25	PCRC5B8200CBSMAS
26	PCLB5B17F001AG5LVL
27	PCLB5B15A255AE
28	MLDLB5B17F954DF5YZ9
29	PCLB5B17F001AG51MD
30	PCLC5B17F001AH5KCV
31	MLDLC5B17F954AB5YBT
32	PCMB5B17A895AD5KBX
33	PCP1MT14N139BABB
34	PCLC5B15A425BD
35	PCLB5B8200FD5YZ9
36	MLDLB5B17F775BE5YZ9
37	PCLC5B15A255AD
38	MLDL1MB105B00BB
Sheet4

Then another sheet with the formulas:

MrExcel_2KGrafix_barcode_scan.xlsm
	A	B	C	D
13	#	B	C	Comp
14	1	RB5B13W020CG RH SAE HIGH U625(2) 12/05/23 16:36:55	PCRB5B13W020CG
Sheet3

Cell Formulas
Range	Formula
C14	C14	= barcode(B14)

Cells with Data Validation
Cell	Allow	Criteria
D14	List	=Sheet4!$A$1:$A$38

And when typing in "=C14" into cell D14 I get an error as desired:

Maybe you didn't check the error message in the settings:

 

[2KGrafix]

Hi Pete,

I'm late replying. I didn't get a notification; that's weird. So I do get that error if I work only within the formula box, but when I use the scanner, the data validation doesn't prevent a wrong part number. I checked all the settings. Everything looks the same as in your example above. It's almost as if the scanner and the =barcode formula are not bound by the same rules as the typed text. I'm puzzled.