Forum Discussion

Xiong_Yang's avatar
Xiong_Yang
Copper Contributor
Dec 09, 2022

Hello all, I'm having issues with a certain VBA Function Code that keeps running.

I had a VBA Function Code that keeps running when I run other Modules or Userforms, or when I click on other cells in the spreadsheet. This causes the function to randomly generate QR codes on the spreadsheet when I don't want them to. I've changed my calculations to "Manual" and not the default "Auto" but am still having the same issue. 

Here is the VBA Code for the function that generates QR codes.

Function QR_Generator(qrcodes_values As String)

Application.EnableEvents = False

Dim Site_URL As String
Dim Cell_Values As Range
Dim k As Range
Set Cell_Values = Application.Caller
Site_URL = "https://chart.googleapis.com/chart?chs=90x90&&cht=qr&chl=" & qrcodes_values
On Error Resume Next
ActiveSheet.Pictures("Generated_QR_CODES_" & Cell_Values.Address(True, True)).Delete
On Error GoTo 0
ActiveSheet.Pictures.Insert(Site_URL).Select
With Selection.ShapeRange(1)
.Name = "Generated_QR_CODES_" & Cell_Values.Address(True, False)
.Left = Cell_Values.Left + 2
.Top = Cell_Values.Top + 2
.Right = Cell_Values.Right + 2
.Bottom = Cell_Values.Bottom + 1
End With
QR_Generator = ""

Application.EnableEvents = True


End Function

  • Xiong_Yang 

    Instead of using a cell formula to create a QR code, you should probably use a macro or a Worksheet_Change event procedure.

Resources