Need help with VBA-generated QR Code

[YansaneYansane]

Hi Tech,

Need help with VBA-generated QR Code based on values in certain cells.........................
Series, Firstname, Lastname, Math Grade, Stat Grade, English Grade, Email, Tel, Notification.

Thank you

 

[Cubist]

QR codes require URL links. I don't see any in your picture.

 

[YansaneYansane]

QR codes require URL links. I don't see any in your picture.

Hi ,

I downloaded "QR4Office"
I'm not using any URLs. Would you recommend me one.

Is there a way to do this locally...?

Thank you....

 

[Cubist]

I'm guessing you wan't to store the info under a QR code. It's typically used for a URL. Here's a sample VBA function. It stores the info separated by the pipe "|" symbol.

Function GETQRCODES(QrCodeValues As String)
    Dim URL As String
    Dim CellValues As Range
    Set CellValues = Application.Caller
    
    URL = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & QrCodeValues
    On Error Resume Next
    ActiveSheet.Pictures("Generated_QR_CODES_" & CellValues.Address(False, False)).Delete
    
    On Error GoTo 0
    ActiveSheet.Pictures.Insert(URL).Select
    With Selection.ShapeRange(1)
        .Name = "Generated_QR_CODES_" & CellValues.Address(False, False)
        .Left = CellValues.Left + 2
        .Top = CellValues.Top + 2
    End With
    
    GETQRCODES = ""
End Function

Usage:

=GETQRCODES(TEXTJOIN("|",,B2:I2))

 

[YansaneYansane]

I'm guessing you wan't to store the info under a QR code. It's typically used for a URL. Here's a sample VBA function. It stores the info separated by the pipe "|" symbol.

Function GETQRCODES(QrCodeValues As String)
    Dim URL As String
    Dim CellValues As Range
    Set CellValues = Application.Caller
   
    URL = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & QrCodeValues
    On Error Resume Next
    ActiveSheet.Pictures("Generated_QR_CODES_" & CellValues.Address(False, False)).Delete
   
    On Error GoTo 0
    ActiveSheet.Pictures.Insert(URL).Select
    With Selection.ShapeRange(1)
        .Name = "Generated_QR_CODES_" & CellValues.Address(False, False)
        .Left = CellValues.Left + 2
        .Top = CellValues.Top + 2
    End With
   
    GETQRCODES = ""
End Function

Usage:

=GETQRCODES(TEXTJOIN("|",,B2:I2))

View attachment 120365

Hi Cubist,

Is it possible to have a sub which can be invoked?

The code is working but placing the QR Code outside the cell around the row.......

Thanks.

 

[Cubist]

To invoke it automatically, it'd need to know where is the last row in your data set. Your sample data seems to have a lot of missing data. What would be the best indicator for the last row?

 

[YansaneYansane]

Hello Cubist,

It's working fine now..
The way you set it up is more intuitive... It populates the QR Code as soon as there's new record in the row. ....and the GetQRCodes formula is present in the column.

However, the formula needs to be copied down, "each time", for it to be taken into account.


Thanks a million