Using Barcode and Data Validation List to Prevent Errors

2KGrafix

New Member
Joined
Jan 26, 2024
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
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?

BCD
Scan=barcode(A1)PCLB5B13W020CG
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Can you make a screenshot of the affected cells? (formulas, data validation, etc.)
 
Upvote 0
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?

1707435944126.png


1707436223799.png
 
Upvote 0
I tried to reproduce it...

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


Then another sheet with the formulas:
MrExcel_2KGrafix_barcode_scan.xlsm
ABCD
13#BCComp
141RB5B13W020CG RH SAE HIGH U625(2) 12/05/23 16:36:55PCRB5B13W020CG
Sheet3
Cell Formulas
RangeFormula
C14C14= barcode(B14)
Cells with Data Validation
CellAllowCriteria
D14List=Sheet4!$A$1:$A$38


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


Maybe you didn't check the error message in the settings:
1707437892811.png
1707437870837.png
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,223
Messages
6,177,256
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top