Object still exists after deletion

Question

Why does a Shape Object still exist after it has been deleted?

When I check the Shape Object it is not Nothing and therefore still exists even though the shape is visually deleted from the worksheet.

Sub Delete_shpObj_and_Check_if_Still_Exists()
    Dim ShpObj As Shape
    Set ShpObj = Sheet1.Shapes("Oval 1")

    'check if shape object exists
    If Not ShpObj Is Nothing Then
        MsgBox "Shape Object exists"
    Else
        MsgBox "Shape Object doesn't exist"
    End If

    'Delete the Shape Object
    ShpObj.Delete

    'Test again if shape object exists
    If Not ShpObj Is Nothing Then
        MsgBox "Shape Object exists"
    Else
        MsgBox "Shape Object doesn't exist"
    End If
End Sub

You have to set it to `nothing`, as well. While the shape itself is deleted, your ShpObj Variable is still assigned. — horst, Dec 03 '20 at 12:22
@horst After the deletion of ShpObj, I set the `ShpObj = Nothing` and it now works beautifully, i.e like intended. Thank you very much for the insight (that the variable is still assigned after deletion if not setting it to Nothing) and for your help! — SweDentan, Dec 18 '20 at 21:13

score 2 · Answer 1 · answered Dec 03 '20 at 13:06

Wrap your test code into a function

Public Function ShapeExists(ByVal InWorksheet As Worksheet, ByVal ShapeName As String) As Boolean
    On Error Resume Next
    Dim ShpObj As Shape
    Set ShpObj = InWorksheet.Shapes(ShapeName)
    On Error Goto 0
    ShapeExists = Not ShpObj Is Nothing
End Function

So you can easily re-use it like

Sub Delete_shpObj_and_Check_if_Still_Exists()    
    Dim ShpObj As Shape    
    Set ShpObj = Sheet1.Shapes("Oval 1")

    MsgBox ShapeExists(Sheet1, "Oval 1")

    ShpObj.Delete 'delete

    MsgBox ShapeExists(Sheet1, "Oval 1")
End Sub

FaneDuru · Answer 2 · 2020-12-03T12:44:14.720

You cannot check the deletion in the way your code tries...

As @horst said, the variable in discussion is still assigned to an object. Even after its deletion.

You can try checking if the object has been deleted, reallocating the object to the (same) variable, in the next way:

On error resume next
 Set ShpObj = Sheet1.Shapes("Oval 1")
 If Not ShpObj Is Nothing Then    
    MsgBox "Shape Object exists"
    on error GoTo 0
 Else
    err.clear: On Error GoTo 0
    MsgBox "Shape Object doesn't exist, anymore..."
 End If

score 0 · Accepted Answer · answered Dec 03 '20 at 14:27

Delete Object With Error Handling

I'm not an expert, but it looks like a case when you would need to use Set ShpObj = Nothing right after the deletion or use Pᴇʜ's function which sets the variable twice.
You would get an error if you would instead of the 2nd confirming message box write something like Debug.Print ShpObj.Type or by repeating the first Set statement, which would prove that it doesn't exist, but is not acceptable.

The Code

Option Explicit

Sub Delete_shpObj_and_Check_if_Still_Exists()

    Dim ShpObj As Shape
    On Error Resume Next
    Set ShpObj = Sheet1.Shapes("Oval 1")
    On Error GoTo 0
'-----------------------------------------------------
'check if shape object exists
    If Not ShpObj Is Nothing Then
        MsgBox "Shape Object exists"
        Dim msg As Variant
        msg = MsgBox("Shape Object exists. Do you want to delete it?", _
            vbInformation + vbYesNo, "Test Shape Object")
        If msg = vbYes Then
            ShpObj.Delete
            Set ShpObj = Nothing
            Application.ScreenUpdating = True
        End If
    Else
        MsgBox "Shape Object doesn't exist"
    End If
'-----------------------------------------------------
'Test again if shape object exists
    If Not ShpObj Is Nothing Then
        MsgBox "Shape Object exists"
    Else
        MsgBox "Shape Object doesn't exist"
    End If
End Sub

Thanks @VBasic2008 for showing me different ways of solving this and your comprehensive explanations which was really knowledge-providing. — SweDentan, Dec 18 '20 at 21:57

Object still exists after deletion

3 Answers3

Delete Object With Error Handling