Generate a dynamic image link and Display in Sheet using VBA

[Restyl]

I want to use Google Chart API to generate QR codes based on a cell value. Here is what my sheet looks like:

My VBA code automatically generates the Stock Number every time the part # is changed. I need the QR Code column to be a QR code for the Stock Number to the right of it. I pasted the values of the Stock Number column in the QR Code Column to symbolize what the QR Code's encoded value should be.

How can I use Google Charts' API to generate a QR Code for each entry based on the Stock Number, and display the actual image in my sheet in the QR code column using VBA?

https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl=I1&choe=UTF-8

 

[Kenneth Hobson]

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
   
  On Error Resume Next
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
  End With
     
  Set r = Intersect(ActiveSheet.Columns(10).Precedents, Target)
  If r Is Nothing Then Exit Sub
   
  For Each c In r.Rows
    With c
      ActiveSheet.Shapes("QR " & .Row).Delete
      QRcodeToPicUTF8 Cells(.Row, "J"), Environ("temp"), _
        "QR " & .Row, Cells(.Row, "K")
    End With
  Next c
     
EndSub:
  With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
  End With
End Sub

 

[nkendri24]

Hey sorry about this but I am using the VBA code from #10 (first section) and adding the recent code and still unable to have a QR populate the cell in K3. Do I need to have anything in the cell for the code to run?

 

[Kenneth Hobson]

Yes, if formula returns no text url, then the google url can not generate a PNG file. Your concatenate needs to list each cell separated by a comma, not a range.

Yes, all of first block of #10 goes into the top of a Module.

As explained in #13 , all the #21 goes into the Sheet's code.

Then, modify any cell in a row that has the formula in column J and away you go...

 

[nkendri24]

Thank you its working now!

One more thing is to fit the QR to a particular size which I see you mentioned but cant seem to change it properly.

Also to add the same QR 1 to C1, QR 2 to G1, QR 3 to K1, QR 4 to O1, then repeats with QR 5 to C3.

 

[nkendri24]

The third line in #3 is for applying the QR code into sheet 2.

 

[Kenneth Hobson]

Do you mean 3rd sentence in post #24 ? If so, then you want to copy 8 shapes from sheet1 to rows 1 and 3 on sheet2? That would be separate method.

For resizing, resize your column(s) and row(s) to suit manually first. A macro could then resize those to what you want as part of the Change event. You can set the QR PNG size to one of 3 or play with it a bit. Best to do rows/columns deal though as you might wind up distorting the shape/image if you don't fit the columns and rows to the image size.

 

[nkendri24]

Hey Kenneth,

I was wondering if you could look at my latest post Excel Drop Down List Automatically Changes Info(multiple) to Code.
you were very helpful with the QR code info.

Thank you

Nick

 

[nkendri24]

Hello Kenneth,

How would I go about creating the same QR code to the appended cell discuss above as well as to another worksheet (QRLabel)? the appended QR macro creates a QR code at cell G3:G83 depending on inputted data. I would as like that once the QR code is formed on this worksheet (QRTemplate) that the same QR codes appear on another worksheet (QRLabel) at cell C1 then C3 then C5... and so on and updates if there are changes.