UPC Scanning and leading zero's

[chipsworld]

G'Day,
Need some guidance...

I have a userform that has a single textbox (txtsrch). The issue is that, I want to scan the UPC barcode on a product and use that to do a vlookup in a sheet.

I immediately ran into a problem where the textbox does not capture the leading "0" in SOME of the barcodes.
Additional info:
Some barcodes are alpha-numeric
Not all barcodes have a leading zero.

Need to be able to pass the textbox into the vlookup, but as scanned. TO include any leading zeros.

Dim txtsrchval As String
 
    txtsrchval = Application.WorksheetFunction.VLookup(Me.txtsrch.Text, Worksheets("Inventory").Range("A:F"), 4, False)

As always...thank you.

 

[Jeffrey Mahoney]

Will this work? Change the 9 to whatever number of digits you have in the barcodes.

Dim txtsrchval As String
  Dim Astr As String
  
  Astr = Me.txtsrch.Text
  Astr = Left("0000000000000", 9 - Len(Astr)) & Astr
  txtsrchval = Application.WorksheetFunction.VLookup(Astr, Worksheets("Inventory").Range("A:F"), 4, False)

 

[chipsworld]

Will this work? Change the 9 to whatever number of digits you have in the barcodes.

Dim txtsrchval As String
  Dim Astr As String
 
  Astr = Me.txtsrch.Text
  Astr = Left("0000000000000", 9 - Len(Astr)) & AstrJe
  txtsrchval = Application.WorksheetFunction.VLookup(Astr, Worksheets("Inventory").Range("A:F"), 4, False)

Jeffrey,
I see what you did, but unfortunately, it won't work because the length of UPC codes varies from short to excessively long. Using a fixed number of spaces is the issue that I have run up against in my trials and tribulations! LOL
Plus, not all UPCs have leading zeros in them.


Thanks

 

[Jeffrey Mahoney]

So, do you think this is a problem with the barcode scanner or is it being interpreted by the text box somehow? I would try scanning it into a different destination like a cell formatted as text. Are there settings on the scanner to account for leading zeros, have it just provide the native value from the barcode?

 

[chipsworld]

So, do you think this is a problem with the barcode scanner or is it being interpreted by the text box somehow? I would try scanning it into a different destination like a cell formatted as text. Are there settings on the scanner to account for leading zeros, have it just provide the native value from the barcode?

Jeff,
Yes... I did some additional testing. I do have some UPCs that start with zero, and the barcode scanner does send the zero.
I do appreciate the help, but I think I have just pushed beyond what Excel and VBA can do.

Thanks again...

 

[Jeffrey Mahoney]

If the scanner sends the zero then you should be able to save it with zero. A cell formatted as a text number format should save it properly.