Excel - Barcodes with VBA :: Barcode software OCX ActiveX

Using and automate 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:="BARCODE.BarcodeCtrl.1", Link:=False, _
 DisplayAsIcon:=False, Width:=MyWidth , Height:=MyHeight, Top:=MyTop + 2,_
 Left:=MyLeft + 4).Select

To get easy access to the control we remind the name of the control in a variable named 'MyBarcode':

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

Now you can set the properties of the control 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's necessary that Windows process upcoming events (often named 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 anew.

Navigation

Downloads

Shop

Excel - Barcodes with VBA

Using and automate barcodes with VBA in Excel