Scanning barcodes to limited fields

Joker2

Board Regular
Joined
Mar 22, 2006
Messages
117
I've limited a field to the correct number of characters for a barcode but I want to ensure that users don't try to scan longer barcodes into that field as it wont be obvious that only part of the barcode is displayed.

I have code when saving the record to check the length of the field to ensure fewer characters are not entered, but how to I make the system bring up a message box or something when a user scans a longer barcode into this field?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

tlowry

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
The approach I would use is to not truncate the BarCode, but to validate it. Below is a function (CheckBCLength) which is passed the BarCode and returns one of three states (BCTooshort, BCTooLong, or BCJustRight) The sub “test” is an example of how to call CheckBCLength and display a message at the result.

Public Enum BClens
BCTooshort
BCTooLong
BCJustRight
End Enum
Option Explicit
Function CheckBCLength(sBC As String) As BClens
Const maxLen = 10
Const minLen = 5
CheckBCLength = BCTooLong
If Len(sBC) > maxLen Then Exit Function
CheckBCLength = BCTooshort
If Len(sBC) < minLen Then Exit Function
CheckBCLength = BCJustRight
End Function
Sub test()
Select Case CheckBCLength("1234567")
Case BCTooshort
MsgBox "Too short"
Case BCTooLong
MsgBox "Too Long"
Case Else
MsgBox "Just right"
End Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,923
Messages
5,621,622
Members
415,848
Latest member
LCC

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
Top