We use cookies to provide you with the best experience and functionality on our website. By continuing to use the site, you are agreeing to our use of cookies.   

Excel
Barcode objects with VBA

VBA Examples

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.

This manual was created by Frank Horn

Search this site with Bing:
| Home | Shop | Download | E-Mail | Datenschutz | Imprint | Version 6.1.0 |
Web DesignszCWs 2.0.12.597
Web design & developement:
www.frank-horn.com

Copyright
Frank HornActiveBarcode Generator App
Web design
Documentation
Copyright Frank Horn
&
Lars SchenkActiveBarcode Object
Web- & server component
Command line
Copyright Lars Schenk

All trademarks and product names mentioned are the property of their respective owners.