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.
|