How Can I Link Multiple Cells into a Bar Code?

Create a bar code linked to multiple cells

Multiple cells in Excel can be linked to one barcode:

  • Use the operator & to concatenate cells.
  • Create the barcode with the data from the cell with the result.

Example - see formula how it is done:

=A3 & B3 & C3

Inserting a Single Barcode into Microsoft Excel

To insert bar codes into a Microsoft Excel document please follow these steps:

  1. Switch to the Add-Ins tab.
  2. Open the TBarCode Panel.
  3. Position the mouse cursor in a cell.
  4. Select the barcode type (e.g. Code 128).
  5. Enter the barcode data or use the default data for the selected barcode.
  6. Adjust the size of the barcode (width, height, module width etc).
  7. Click the button Insert Barcode. Finished!
Insert Barcodes into Microsoft Excel with TBarCode Office

How to Create a Barcode List

  1. Open the Excel spreadsheet with the barcode data (e.g. a list with article numbers) or create your own list.
  2. Open the TBarCode Panel.
  3. Mark the cells with the barcode data.
  4. Select the barcode type (e.g. Code 128).
  5. Click the button Insert Barcode. Finished!
Excel Add-In: Create a List with Barcodes

Video: Creating Barcodes and Barcode Lists in Excel

Barcode Excel Add-In: Create Barcodes in Excel

The first part of the video demonstrates how to create a single barcode. 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) with a single click.

For more videos visit our TBarCode Office Tutorial Channel!

No Barcode Visible / Cropped Barcode

TBarCode is inserted in Word as „Inline Shape“. In case of Inline Shapes, the shape is clipped if the line height is configured to a fixed value.

The barcode should be visible again if you adjust line spacing to "Single".

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.

Reason

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 Excel Support team here. If possible, avoid the remote desktop. In each case you should save the document before printing and not afterwards.

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

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.

Workaround

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

  • Open the TBarCode Panel and go into the Add-In options
  • Enable those two options: Static Barcodes + Scale EMF Barcode
  • Insert a 2D Code and print it.

When you print the 2D Code, the scaling from Excel should be compensated by our own scaling now (activated by option “Scale EMF Barcode”). 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.

Existing bar codes are not changed by this option! You need to insert the bar code again from scratch (at least the ones, which do not print correctly). With Static/EMF enabled, the bar code cannot be linked permanently to a cell. The cell data is copied to the barcode only during insert.

Excel Scrolls Always Down to Barcode Control

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:

' Add to Module 1
Sub GoToActualSelection()
Dim myRange As Range
Set myRange = Selection
myRange.Select
End Sub
' Add to Worksheet
Private Sub Worksheet_Activate()
GoToActualSelection
End Sub
' Add to Workbook
Private Sub Workbook_Open()
GoToActualSelection
End Sub

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

Barcode Size Changes On Different PC

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.

Please note that in TBarCode Office V10.5.3 a workaround for this problem has been introduced. Beside that also Microsoft offers a workaround for this problem in KB838006.

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 ActiveX Control or EMF picture – configurable in V10.9 via options
    • When using EMF Pictures, no VBA programming possible, barcodes linked to cells are updated before printing, a higher number of bar codes per sheet is supported. EMF is the recommended method for Office 2016/365.
    • When using the ActiveX Control, VBA programming is possible, barcodes are updated in real-time on screen, the number of bar codes per sheet should be low (resource intensive).
      Bar codes are licensed 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 licensed (Demo imprint) 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.