Barcode objects with VBA
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"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:
MyHeight = Range(CurrentCell).Height
MyWidth = Range(CurrentCell).Width
MyTop = Range(CurrentCell).Top
MyLeft = Range(CurrentCell).Left
' Enlarge the cell height to 30 pixelsThe following function will create the barcode control directly above the cell:
Range(CurrentCell).RowHeight = 30
ActiveSheet.OLEObjects.Add(ClassType:="BARCODE.BarcodeCtrl.1", Link:=False, _To get easy access to the control we remind the name of the control in a variable named 'MyBarcode':
DisplayAsIcon:=False, Width:=MyWidth , Height:=MyHeight, Top:=MyTop + 2,_
Left:=MyLeft + 4).Select
MyBarcode = ActiveSheet.Shapes(ActiveSheet.Shapes.Count).NameNow you can use the properties and methods of the barcode object as you like:
' set propertiesIf you do not need the control anymore you can delete it from the sheet:
ActiveSheet.OLEObjects(MyBarcode).Object.Font.Size = 8
ActiveSheet.OLEObjects(MyBarcode).Object.Type = 14 ' Code 128
ActiveSheet.OLEObjects(MyBarcode).Object.Text = Range("C3")
ActiveSheet.OLEObjects(MyBarcode).DeleteHint: 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:
DoEventsThis can be necessary, e.g. if the Control must draw itself anew.