The object invoked has disconnected from its clients

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
97
Office Version
  1. 365
Platform
  1. 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.

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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Watch MrExcel Video

Forum statistics

Threads
1,122,991
Messages
5,599,242
Members
414,298
Latest member
bongielondy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top