Creating QR code Using Excel VBA

What is QR Code?

A QR code, on the other hand, is a specific type of 2D barcode. QR stands for “Quick Response,” and QR codes are designed to store data in a matrix or grid pattern of black squares on a white background.

Why QR Code?

In Excel, you can use QR codes for various purposes, such as linking to websites, tracking inventory, creating digital business cards, and more. Here’s why you might want to use QR codes in Excel:

1. Quick Access to Information: QR codes can be generated to contain URLs, text, or other data. Details
2. Inventory Management: If you’re managing inventory or assets in Excel, you can create QR codes with unique identifiers. Details
3. Contact Information: QR codes can store contact information, making it easy to share your business card digitally. Details
4. Forms and Surveys: QR codes can link to forms and surveys. Details
5. Menu and Pricing Updates: If you manage a restaurant or a similar business, you can create QR codes that link to your menu or pricing list. Detail
6. Data Entry and Validation: You can use QR codes to simplify data entry and reduce errors. Details

7. Marketing and Promotions: QR codes can be used in marketing materials, such as brochures or posters, to provide additional information or promotions. Details

Now back to Business

The Goal

Steps in Creating The QR code

• Click the Developer tab then click the Visual Basic tab

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim qrCodeURL As String
    Dim qrCodeImage As Picture
    Dim pictureName As String

The code is triggered when there is a change in the worksheet, as indicated by the Worksheet_ Change event.

qrCodeURL is a String variables that will hold the URL (or content) for generating QR codes.

qrCodeImage is a Picture variable used to store the generated QR code images.

pictureName is a String variable for naming the generated pictures.

If Not Intersect(Target, Me.Range("B9 B10 B13 B14 C10 E10 G10")) Is Nothing Then

This combines all the specified ranges into a single string and checks if Target intersects with any of these ranges. If it does, the condition evaluates to True.

From the Sample image and the code, if there is change in the cell( B9……,G10) Then Combine the content of the cells(I24,….I27,J24,….,J27) for the QR code

 combinedContent = Me.Range("I24").Value & " : " & "N" & Format(Me.Range("J24").Value, "#,##0.00") & vbCrLf & _
                   Me.Range("I25").Value & " : " & Me.Range("J25").Value & vbCrLf & _
                   Me.Range("I26").Value & " : " & Me.Range("J26").Value & vbCrLf & _
                   Me.Range("I27").Value & " : " & Me.Range("J27").Value

Connecting the QR Code api with the combined content of the cells above

 qrCodeURL = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & combinedContent

Generate a unique picture name based on the current time for the QR code

pictureName = "Picture_" & Format(Now(), "yyyyMMddhhmmss")

Delete existing picture with the generated name (if any)

On Error Resume Next
Me.Pictures(pictureName).Delete
On Error GoTo 0

Add a new picture to the worksheet with the generated name

Set qrCodeImage = Me.Pictures.Insert(qrCodeURL)

Assign the generated name to the picture

qrCodeImage.Name = pictureName

Set the picture’s position to cell L24

With qrCodeImage
            .Left = Me.Range("L24").Left
            .Top = Me.Range("L24").Top
            .Width = 78 ' Adjust the width as needed
            .Height = 65 ' Adjust the height as needed
        End With
   End If
End Sub

To create QR codes in Excel, you’ll typically need to use a QR code generator or a third-party add-in. There are several free and paid tools available that can help you generate QR codes based on the data you want to encode. Once generated, you can insert the QR codes into your Excel worksheets or print them for physical use.