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:
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:
This can be necessary, e.g. if the Control must draw itself anew.
This manual was created by Frank Horn