H!
Interesting problem. Not sure which barcode generator you need to use or prefer to use, but I've found one called
zint that I like. It's free, open source, doesn't require an online connection, includes both a decent GUI and command line solutions,
and has pretty good documentation. I had never tried it from the command line before (which we need to do if running it from a VBA script), but I was able to piece things together nicely from its documentation.
I played around a little with it myself and ended up with the following, called from the
Worksheet_Change event:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$10" Then
'Delete existing QR code in worksheet
Dim pic As Shape
For Each pic In Sheet3.Shapes
If pic.Name = "QR Code" Then pic.Delete
Next pic
'Create new QR code
Dim data As String
Dim qr As Picture
Dim qrImagePath As String
data = Sheet3.Range("B10").value
qrImagePath = "C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\qr.png"
Shell "C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\zint.exe zint -b 104 -d """"" & data & """"" -o " & qrImagePath & " --scale 5"
Set qr = Sheet3.Pictures.Insert("C:\Users\%username%\Downloads\zint_win32_snapshot_2021-07-02\2021-07-02\qr.png")
qr.Left = Sheet3.Range("C10").Left
qr.Top = Sheet3.Range("C10").Top
qr.PrintObject = True
qr.Name = "QR Code"
End If
End Sub
You'll need to change: the worksheet you want to use, the path pointing to zint.exe, the location where you want to save the QR image, and the location in the worksheet you want the image to be. You also might want to play around with the cmd line a little to customize the output exactly how you want it.
-b 104 is telling it to export as HIBC QR Code type,
-d specifies the data for the QR code,
-o specifies the output path. Those are the basics, but you can add other parameters according to the documentation to customize the format or size.
