Larry Haydn
Board Regular
- Joined
- Jul 18, 2019
- Messages
- 207
- Office Version
- 365
- Platform
- Windows
I have a QR Code on a worksheet.
When data is changed, the QR Code has to be deleted and re-created with the new data.
There are two methods whereby data will be changed in the worksheet.
Search for a record (in SQL Server) by scanning:
1. a barcode in a print out (or manually input value), or
2. a QR code in a printout (or manually input value)
After searching (and if the record is found), the worksheet fields will be cleaned out,
then populated with the newly found data.
After populating the worksheet with new data, the QR code has to be deleted and re-generated.
The code to delete the QR code in both case is THE SAME CODE.
This code runs perfectly when searching by barcode, but raises the stated error when searching by QR Code.
Thing is, this code had ran for thousands of times without error.
As soon as the line ( QRImage.Delete ) is executed, the program crashed.
And I cannot figure out why.
Here is the full code:
When data is changed, the QR Code has to be deleted and re-created with the new data.
There are two methods whereby data will be changed in the worksheet.
Search for a record (in SQL Server) by scanning:
1. a barcode in a print out (or manually input value), or
2. a QR code in a printout (or manually input value)
After searching (and if the record is found), the worksheet fields will be cleaned out,
then populated with the newly found data.
After populating the worksheet with new data, the QR code has to be deleted and re-generated.
The code to delete the QR code in both case is THE SAME CODE.
VBA Code:
On Error Resume Next
For Each QRImage In ActiveSheet.Shapes
If (QRImage.Type = 11) Or (QRImage.Type = 12) Or (QRImage.Type = 13) And _
QRImage.Name <> "cmdSearchByQRCode" Then
QRImage.Delete
Exit For 'Since there is only 1 QR code to delete
End If
Next QRImage
On Error GoTo 0
This code runs perfectly when searching by barcode, but raises the stated error when searching by QR Code.
Thing is, this code had ran for thousands of times without error.
As soon as the line ( QRImage.Delete ) is executed, the program crashed.
And I cannot figure out why.
Here is the full code:
VBA Code:
Sub RemoveQRLabels()
'================================================
' Removes all QR Code OCX from the worksheet
'================================================
Dim QRImage As Shape
Dim ImgType As Integer
'===============================
' Remove all QR Code images
'===============================
On Error Resume Next
For Each QRImage In ActiveSheet.Shapes
If (QRImage.Type = 11) Or (QRImage.Type = 12) Or (QRImage.Type = 13) Then
Stat "Deleting " & QRImage.Name
QRImage.Delete 'Error when this line is executed
Exit For
End If
Next QRImage
On Error GoTo 0
End Sub