FAQ: TBarCode Office Excel Add-In

Video: Creating Barcodes and Barcode Lists in Excel

The first part of the video demonstrates how to create a single barcode in Excel. If required, the barcode can be linked to a spreadsheet cell. Dynamic bar-codes, which are updated automatically, are implemented easily with this cell linking feature. In the second part of the video you learn how to create multiple bar codes (list) in Excel with a single click.

For more videos visit our TBarCode Office Tutorial Channel!

How To Create a Swiss QR Code

Swiss QR Code is available in TBarCode Office V11 - follow the link and download the new version.

Below we give you instructions on how to create Swiss QR Code 2.2 in a serial letter:

1. Download the Excel Sheet for Swiss QR Bills (click the link). The sheet contains calculated columns, which create the data format for the Swiss QR Code 2.2 payment standard.
2. Enter the payment information into columns A - Q.
   Note the calculated columns SwissQRCode_Part1, SwissQRCode_Part2 and SwissQRCode_Part3, which will contain the bar code data for the bar code add-in. There are also more calculated columns at the right, which are used to validate your input. Line Feeds are encoded through the \n escape sequence.
3. In your Word mail merge document, select the Excel sheet as data source for the mail merge.
4. Open TBarCode Panel, select Swiss QR Code and add the serial field placeholders via Insert Fields as shown below:
   
   We split the data content into multiple fields, because in the serial letter you are limited to 255 characters in a single field. In the barcode this will be put back together correctly. The field SwissQRCode_Part3 is required if further data is coded after the EPD (Bill-Info, AV Parameter 1, AV Parameter 2).

Barcode Control Blocked or Barcode Not Updated

Please verify your security settings.

Network Path

By default documents from a shared network path containing active content (such as barcode controls) are opened in a safe mode, where active content may be disabled. If you see a yellow security warning on top of your document, go to the trust center in Word and/or Excel and allow the network path you use.

• Create, remove, or change a trusted location for your files

Security Alerts

TBarCode Office V10 is using ActiveX Controls for bar code creation. Depending on your security settings, ActiveX Controls may be blocked. If you see a yellow security warning on top of your document, you have to click "Enable Content" to allow access to the Barcode ActiveX Controls. The behavior of ActiveX Contols can be adjusted as shown below:

• Enable or disable ActiveX controls in Office documents

Print Preview or Printing Changes the Barcode Position

Problem

The following problem has been encountered: With Remote Desktop connections and Terminal Server sessions the bar code position changes after opening the print preview or printing the document. Over time the bar codes shift slightly towards the top left or bottom right, causing the bar codes to wander.

The reason may be a program error of Excel. The position of the bar code controls is managed by Excel independently - the add-in has no influence on that (except during insert). The usage of the remote desktop probably triggers the problem.

Solution

Please try the solution offered by the Microsoft Support team. If possible, avoid the remote desktop. In each case you should save the document before printing and not afterwards.

Barcode Is Printed with a Different Size than in Preview

Problem

You have insert a bar code in size mode „Fit to Bounding Box“ and linked it to a cell – then you have enlarged the bar code with the mouse. During printing – if the linked cell value changed – the bar code will be resized and becomes smaller.

When updating a barcode image via print events, the last taken panel size is used - and that’s why the bar code becomes smaller. In other words - when printing, the system checks whether the data has changed - if so, a completely new barcode image is generated - but in the original size at the time of insertion (and not in the later scaled size).

Workaround

After manually adjusting the barcode size, you should click on Update again in the TBarCode Panel - then the scaled (and in most cases distorted) image will be re-generated and the changed size will be adopted into the internal barcode properties.

Wrong Aspect Ratio with 2D Matrix Codes

Problem

In the preview and on the print out the 2D matrix codes are printed with distorted aspect ratio (the printed symbol is not square!).

Reason for that behavior is a scaling applied by Excel to the whole page. It also depends on the default font of the sheet if you see this problem or not.

• Excel distorts image when printing

Workaround

If you have 10.9.0 or later installed, you can use the following workaround:

• Open the TBarCode Panel and go into the Add-In options
• Enable the option Scale Picture (correct print size)
• Insert a 2D Code and print it.

When you print the 2D Code, the scaling from Excel should be compensated by our own scaling (activated by option). We use a standard compensation which works in most cases. If needed, there would be the possibility to further fine-tune the scaling ratio / compensation with a registry key.

Scaled or Cropped EMF Barcodes with Docking Station

Problem

The rendering of EMF images depends on the DPI settings of the screen. Normally Excel compensates DPI differences automatically, but not in all cases: If the screen resolution changes during operation - e.g. by connecting a notebook to a docking station or by connecting to a PC with Remote Desktop - the EMF bar code may be scaled and partially cropped.

Workarounds

• If you want to connect a notebook to a docking station, first save your Excel document with created barcodes. Then shut down the computer and boot it after it has been connected to the docking station (i.e. after the changed screen settings have been adopted). After restarting at the docking station, the barcode should be displayed correctly.
• In the Windows display settings activate Let Windows try to fix apps so they’re not blurry. Then restart Excel and update all barcodes. (hold down the SHIFT key and then click "Refresh all barcodes" in the TBarCode panel).

Scaled or Cropped EMF Barcodes with Remote Desktop

Problem

If you connect to a PC through Remote Desktop, the EMF barcodes may look scaled or cropped after updating the bar codes. The problem occurs if you have scaled the size of text, apps and other items, that means the scaling in the Display Settings is set to 125% or higher.

Example for a scaled size, which triggers the problem.

Workaround

In order to fix the EMF bar code scaling, enable the following setting in Windows 10:

• Right click on the desktop and select Display Settings.
  Alternatively open the Settings app, go to the System group of settings, then select the Display tab.
• Scroll down to the Scale and layout section and click Advanced scaling settings.
• On the following screen, below “Fix scaling for apps”, enable the Let Windows try to fix apps so they are not blurry switch:

After that restart Excel (or Word) and refresh the bar codes in your document: Press SHIFT + Refresh in the add-in panel to force a refresh of all bar codes.

Barcode Size Changes on a Different PC

General Scaling Issues

We have seen scaling issues

• If a user has adjusted enlarged display fonts or enlarged DPI (often used with 4K screens or screens, where the font is small) ➔ How to Change the Font Size on Your Screen (lifewire.com)
• If DOC format is used instead of DOCX
• If the printer driver does not provide correct DPI
• If the user changes the screen resolution while login (e.g. Remote Desktop or Docking Station) – see FAQ above.
• After a Windows update (sometimes a reboot helps or an Office repair or disabling Fast Startup).
• If the embedded bar code is of type ActiveX and not EMF

Issues with ActiveX Controls

There are two possible reasons why this size changing happens:

1. Enlarged Screen Fonts

User A is using standard font sizes (100%). User B has adjusted enlarged screen fonts (e.g. 125%). User A saves the Excel sheet with the bar codes in normal (correct) size. User B opens the sheet and discovers the bar code object with a different size (scaled according to the difference in screen font sizes).

Excel scales the bar code objects independently from TBarCode Office. The ideal solution would be to use the same readability options (screen font enlargement) on all involved PCs.

2. Different Screen Resolution

User A has a screen resolution of 1280x1024 Pixels. User B has a screen resolution of 1600 x 1200 Pixels. User A saves the Excel sheet with a bar code in normal (correct) size. User B opens the sheet and discovers the bar code object with a different size (scaled according to the difference in screen resolution between A and B).

Excel scales the bar code objects independently from TBarCode Office. The ideal solution would be to use the same screen resolution on all involved PCs.

Workaround

In TBarCode Office V10.5.3 a workaround for this problem has been introduced (Option "Redraw on document open"). Beside that also Microsoft offers a workaround for this problem in KB838006.

Excel Not Responding, Freezes or Stops Working

Too Many Bar Codes

Excel 2016 and 2019 is more sensitive about the number of bar codes inserted into one spreadsheet than other versions. Each bar code - especially when insert as ActiveX Control - is consuming GDI objects for drawing the bar code. Once the limit of available GDI objects is reached, Excel cannot respond anymore and must be terminated in Task Manager.

Solution: Reduce the number of bar codes in your sheet and embed the barcodes as EMF picture (default format in 10.9.0 or later). More information about add-in limitations and hints for troubleshooting can be found in the user manual.

Incompatible Plugins

Please verify if you have installed other third party plugins, which may interfere with the TBarCode/Office add-in. We received customer reports about issues with the Dymo® Label printer plugin, the Showcase Query Add-In, the Autodesk® Inventor® and Adobe® PDFMaker (when installed in parallel).

How Can I Auto-Update the Barcodes Once a Cell Is Changed?

With EMF barcodes, the barcodes are updated during printing and on saving the document. If you need to update the barcodes immediately when a specific cell or range has been changed, you can do that with a macro:

• Enable „Refresh barcodes on save“ in the add-in options.


• Add the following code to your Excel sheet (ALT-F11) and save it as macro enabled workbook.
  
  Private Sub Worksheet_Change(ByVal Target As Range)
' specify the range, which will trigger the auto-save here
If (InRange(Target, Range("A1:A10"))) Then
  Application.DisplayStatusBar = True
  Application.ActiveWorkbook.Save
  Application.StatusBar = "Auto-Save done!"
End If
End Sub Function InRange(Range1 As Range, Range2 As Range) As Boolean
  InRange = Not (Application.Intersect(Range1, Range2) Is Nothing)
End Function


The macro will save the document and this will trigger the bar code update.

Excel Scrolls Always Down to Barcode Control (V10)

Problem

When opening a document or activating a worksheet, the focus is not on the last selected cell - instead Excel displays the (last) embedded Barcode ActiveX Control.

Workaround (VBA)

The following workaround moves the cursor back to actual selection. Press Ctrl-F11 to open the VBA editor and add the following code:

Save your document as Macro-enabled Workbook (XLSM).

Which Product Is Better Suited to My Needs- the SDK or the Add-In?

Here the comparison:

TBarCode Office - Excel Add-In

• Contains a panel for inserting bar codes
• Embed bar codes as EMF picture, a higher number of bar codes per sheet is supported.
• No VBA programming of the bar codes possible, barcodes linked to cells are updated before printing.
Bar codes linked to dynamic data are updated only if the add-in is loaded. If Excel disables the add-in for some reason (e.g. after updates), then the bar codes are NOT updated and you need to re-activate or re-install the add-in again.

TBarCode SDK - ActiveX Control

• No panel for inserting bar codes, bar codes are insert through Developer ribbon in Excel
• ActiveX Control for embedding bar codes, the amount of bar codes per sheet should be low (< 100)
• VBA programming is possible, linked barcodes are updated in real-time, bar codes are licensed if there is the license key available in the registry ⇒ usually no problem with unlicensed bar codes, not much user interaction required.

Summary

• We recommend the SDK if the focus lies on VBA programming or the sheet should work without need for too much user interaction and the number of bar codes per sheet is low.
• We recommend the Add-In (with EMF Barcodes enabled) if the focus lies on many bar codes per sheet (e.g. barcode list) with a user interface (panel) for easy configuration.