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:="ACTIVEBARCODE.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.