Barcodes in Excel 2016, Excel 2013 and Excel 365

Embed and automate a barcode in an Excel sheet

Excel 365, 2024, 2021, 2019, 2016, 2013, 2010

Here is a short description of how to add a barcode to an Excel document and link it with the content of a cell.

First, launch Excel and create or open a document.

Make sure that you have configured Excel so that the Developer tab appears. To do so, go to Excel Options by clicking the File tab. This will switch you to the Backstage view. Then, click Options in the tab group on the left. ActiveBarcode: Barcode, Excel

Under Customize Ribbon, enable the Developer option on the main tabs, then close the options window. ActiveBarcode: Barcode, Excel

You can now embed the ActiveBarcode control element into your document. Select the Developer tab. Click the Insert button from the ActiveX Controls range, and then select More Controls. ActiveBarcode: Barcode, Excel

A dialog box appears on the screen: ActiveBarcode: Barcode, Excel

Select ActiveBarcode and click OK. A barcode will now be created inside your document. It will be a standard EAN-128 barcode. This is the default type. You can change it to any barcode type supported by ActiveBarcode: QR Code, GS1/EAN-128, Data Matrix, GTIN/EAN-13, Code 39, Code 93, GS1-Data Matrix, Code 128, PDF417, DUN-14, EAN-8, EAN-14, EAN-18, ISBN, ITF-14, NVE, PZN8, SCC-14, SSCC-18 ...You can now modify this barcode. You can change the type, text, colors, etc. To do so, right-click inside the barcode. A pop-up menu will open: ActiveBarcode: Barcode, Excel

Select ActiveBarcode Object followed by Properties. The ActiveBarcode property dialog will open. You can modify the barcode however you like here.

Alternatively, you can use Excel's property dialog. To do so, click the Properties button in the Controls range: ActiveBarcode: Barcode, Excel

Create a link between a cell and the barcode: Open the Excel property dialog as described above. Activate the barcode object by clicking on it once. The properties of the barcode object will now be displayed in the property dialog. Look for the property named LinkedCell. This property creates a link between a cell and the barcode: ActiveBarcode: Barcode, Excel

Enter the name of the cell for the link into this property. That's it!

If you want to create a barcode that encodes data from several cells, you must first merge these cells into an helping cell. Then connect this combined cell with the barcode object.

In the following example the contents of cells B3 and B4 are combined in cell B6. Cell B6 is linked to the barcode object. ActiveBarcode: Barcode, Excel

Using and automating barcodes with VBA in Excel

You can use VisualBasic for Applications (VBA) to solve many things in Excel. Here we will show you how to embed, use and delete the ActiveBarcode control with VBA:

Embedding the ActiveBarcode Control into a Sheet:
In this example a barcode control will be placed directly above a cell. Then it looks like the barcode would be inside a cell.

First we read the cells size:

CurrentCell = "C3"
MyHeight = Range(CurrentCell).Height
MyWidth = Range(CurrentCell).Width
MyTop = Range(CurrentCell).Top
MyLeft = Range(CurrentCell).Left

Please note that a cell should have a minimum size so the barcode can fit into it. If the cell is too small a Size error will be displayed. So we resize the height of the cell to 30 pixels:

' Enlarge the cell height to 30 pixels
Range(CurrentCell).RowHeight = 30

The following function will create the barcode control directly above the cell:

ActiveSheet.OLEObjects.Add(ClassType:="ACTIVEBARCODE.BarcodeCtrl.1", Link:=False, _
 DisplayAsIcon:=False, Width:=MyWidth , Height:=MyHeight, Top:=MyTop + 2,_
 Left:=MyLeft + 4).Select

You can directly link the barcode object with a cell:

ActiveSheet.OLEObjects(MyBarcode).LinkedCell = "B7"

To get easy access to the control we store the name of the control in the variable MyBarcode:

MyBarcode = ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Name

Now you can use the properties and methods of the barcode object as you like:

' set properties
ActiveSheet.OLEObjects(MyBarcode).Object.Font.Size = 8
ActiveSheet.OLEObjects(MyBarcode).Object.Type = 14 ' Code 128
ActiveSheet.OLEObjects(MyBarcode).Object.Text = Range("C3")

If you do not need the control anymore you can delete it from the sheet:

ActiveSheet.OLEObjects(MyBarcode).Delete

Hint: If it is necessary for Windows to process upcoming events (often referred to as KeepWindowsAlive) within a macro, you can force this by using the following VBA function:

DoEvents

This can be necessary, e.g. if the Control must draw itself new.