Printing Through Excel VBA: Barcode Is Not Updated

Printing Through Excel VBA: Barcode Is Not Updated

You change the content of linked cells or the barcode control, then print directly from within VBA. But you see that the barcode controls still contain old values. The reason is that Excel does not send a redraw command to the ActiveX Controls before printing (if done from within VBA). This is a bug in Excel.

There is a workaround to force a redraw of the objects, you need to address each Barcode ActiveX Control programmatically in VBA and change its size before printing. Then it is redrawn by Excel.

'redraw a single barcode object by addressing it through its instance name

Dim origValue
origValue = TBarCode101.Width
TBarCode101.Width = origValue + 1
TBarCode101.Width = origValue

' redraw all ActiveX Controls on the current sheet

Sub UpdateOLEControls()
Dim myShape As shape
Dim counter As Integer
Dim origWidth As Single
For counter = 1 To ActiveSheet.Shapes.Count
  Set myShape = ActiveSheet.Shapes(counter)
  If (myShape.Type = msoOLEControlObject) Then
    ' resize => force redraw
    origWidth = myShape.Width
    myShape.Width = origWidth + 1
    myShape.Width = origWidth
  End If
Next counter
End Sub