Creating a Barcode Scanner Macro in Excel (with Easy Steps)

Machine-readable code is numbers and a pattern of parallel lines displaying manufacturer and product info upon scanning. Excel worksheets can be used as barcode scanners using a macro.  Excel barcode scanner macro can display product details in its sheets upon entering scanned barcode numbers.

Excel Barcode Scanner Macro


Download Excel Workbook

Use the attached Excel file as a free Template. Make sure you modify the attached macro according to your needs. The dataset works as an Excel barcode scanner auto-enter.


Barcode and Its Convention

Mainly a 12-digit number format with patterns known as the Universal Product Code, which is used as barcodes around the world. There are other versions of barcodes available. However, in this article, we demonstrate the UPC-A version of the Universal Product Code as barcodes. The UPC-A version has a depiction like the below image.

UPC-A Barcode

The first 6 digits of the UPC-A barcode define the Company, the latter 5 digits the product, and the last digit is the Check Digit.


Step-by-Step Procedures to Create a Barcode Scanner Macro in Excel

Go through the below steps to create an Excel barcode scanner. We will compile the scanned barcode numbers into a worksheet and then will create an inventory record sheet. Finally, we will use a Macro to accomplish the task.


Step 1: Compiling the Barcode into Excel Worksheet

Firstly, you need to compile the scanned barcode numbers into a worksheet as depicted in the image below. Make sure you have all the necessary information regarding the products and manufacturer, as you will be needing them in the macro.

Barcode Data for Excel Barcode Scanner Macro

Read More: How to Create Barcode Without Font in Excel (2 Smart Methods)


Step 2: Creating an Inventory Record Worksheet with Desired Product Info

Now you want the product information stored in barcodes whenever you run a macro.

  • Name the column with desired product info of what you want to fetch.
  • You need to assign these columns in the macro to display product info in them.

Read More: How to Make Auto Enter Barcode Scanner in Excel


Step 3: Inserting a Macro Button into the Barcode Worksheet

It’s annoying to run a macro using the Microsoft Visual Basic window. Therefore, use a Macro Button to run the macro any time after the new barcode insertion.

Read More: How to Use Barcode Scanner in Excel (2 Suitable Ways)


Step 4: Assigning a Macro to Fetch Info into the Inventory Record

The inserted macro button needs a macro to be assigned. The macro needs to be written in a way that satisfies all the desired requirements.

  • Right-click on the Macro Button then select View Macro.

  • Paste or modify the following macro to display desired product info of the inserted barcodes.
Private Sub CommandButton1_Click()
Dim mBarcode As String
Dim mCode, mMan As String
Dim mRng As Range
Dim mDes, ManDes As String
Dim mRowNumber, mCount As Long
Dim mRow As Long
Dim mLastRow As Long
R = 6
mLastRow = Sheet2.Cells(Rows.count, 2).End(xlUp).Row
 For R = 6 To mLastRow
 mBarcode = Sheet2.Cells(R, 2)
 Sheet1.Activate
 mRow = ActiveSheet.Cells(Rows.count, 2).End(xlUp).Row + 1
  If mBarcode <> "" Then
  Set mRng = ActiveSheet.Columns("B:B").Find(what:=mBarcode, _
  LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
  searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
   If mRng Is Nothing Then
    Sheet1.Cells(mRow, 2) = mBarcode
    Sheet1.Cells(mRow, 3) = Mid(mBarcode, 7, 5)
    mCode = Sheet1.Cells(mRow, 3)
     If Left(mCode, 4) = "6345" Then
     mDes = "Black Point Pen"
     End If
     If Left(mCode, 4) = "5341" Then
     mDes = "Blue Point Pen"
     End If
     If Left(mCode, 4) = "2365" Then
     mDes = "Red Point Pen"
     End If
    Sheet1.Cells(mRow, 4).Value = mDes
    Sheet1.Cells(mRow, 5) = Mid(mBarcode, 2, 5)
    mMan = Sheet1.Cells(mRow, 5)
    If Mid(mMan, 1, 5) = "23456" Then
     ManDes = "Sheely Pen Corp."
     End If
    Sheet1.Cells(mRow, 6) = ManDes
    Sheet2.Cells(R, 2).ClearContents
   GoTo ende
   Else
   MsgBox "Barcode Already Existing"
   End If
  End If
ende:
Next R
End Sub 
			

Macro

Macro Explanation

  • Firstly, the macro defines the variables.
Dim mBarcode As String
Dim mCode, mMan As String
Dim mRng As Range
Dim mDes, ManDes As String
Dim mRowNumber, mCount As Long
Dim mRow As Long
Dim mLastRow As Long
mBarcode = Sheet2.Cells(R, 2)
  • Sheet1 gets activated and Excel finds barcodes using VBA Cell Property.
Sheet1.Activate
mRow = ActiveSheet.Cells(Rows.count, 2).End(xlUp).Row + 1
If mBarcode <> "" Then
Set mRng = ActiveSheet.Columns("B:B").Find(what:=mBarcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False, SearchFormat:=False)
  • VBA IF imposes conditions to display text depending on the specific digits of barcodes.
If Left(mCode, 4) = "6345" Then
mDes = "Black Point Pen"
End If

or

If Mid(mMan, 1, 5) = "23456" Then
ManDes = "Sheely Pen Corp."
End If

⧭Note: This type portion of the macro is highly sensitive. You need to modify or change similar macro lines to fit your demands. Otherwise, this macro will be useless in your case.

  • After displaying the info, Excel clears the inserted barcodes.
Sheet2.Cells(R, 2).ClearContents 
			

Read More: [Solved] Barcode Scanner Not Going to Next Line in Excel


Step 5: Running VBA Macro to Work the Excel Barcode Scanner

After assigning the macro to the Macro Button, you need to activate the macro by pressing F5.

  • Go to the Scanned Barcode Data sheet, and enter barcodes.
  • Click on the Fetch Product Info macro button.
  • Instantly, Excel displays the product info as shown in the picture below.

Excel Barcode Scanner Macro


Conclusion

This article discusses the UPC-A type barcode and its Excel barcode scanner macro. Users may have different versions of barcodes. In those cases, they need to modify the macro according to the barcode convention. Comment if you need further clarification or have anything to add.

Do check out our awesome website, ExcelDemy. There are hundreds of articles regarding Excel and its issues.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo