Creating a Barcode Scanner Macro in Excel: 5 Methods

Method 1 – Compiling the Barcode into Excel Worksheet

Compile the scanned barcode numbers into a worksheet, as depicted in the image below. You have all the necessary information regarding the products and manufacturer, as you will need them in the macro.

Barcode Data for Excel Barcode Scanner Macro


Method 2 – Creating an Inventory Record Worksheet with Desired Product Info

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

 


Method 3 – Inserting a Macro Button into the Barcode Worksheet

Run a macro using the Microsoft Visual Basic window. Use a Macro Button to run the macro any time after the new barcode insertion.

 


Method 4 – Assigning a Macro to Fetch Info into the Inventory Record

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

  • Paste or modify the following macro to display the 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

  • 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
  • The barcodes start from row of Sheet2. Excel VBA Cells Property assigns it.
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 
			

Method 5 – Running VBA Macro to Work the Excel Barcode Scanner

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

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.


Related Articles


<< Go Back to Use Barcode ScannerBarcode in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo