VBA - Unable to delete objects w/ INTERSECT if using Data Validation

[StarfoxC17]

Good morning! Newbie here, but I've exercised about all the Googling I can do to find the solution and I just have not found one anywhere. Wondering if any expert VBA guys/gals can help a rookie out...


Problem: When manually selecting cells that use List-style Data Validation, I can no longer run a macro that deletes objects (pictures, checkboxes, etc.) using the "INTERSECT" technique. A 1004 Error pops up.


Macro: My workbook is essentially a calendar for flights and academic courses. Full disclosure, I run a macro that merges (I gather that people are allergic to merging, but it suits my specific purpose). The macro copies a template for a day's flight event from another tab, copies it into the current tab, which IMPORTANTLY includes objects... checkboxes/images and the like... honestly, I'm not a professional, it's pretty rudimentary. Ideally, it would be able to copy cells that have lists w/ data validation so I could pick crew members/students/etc. from a fixed list. It can copy those data validated cells just fine, but I run into issues when I try to clear them.


A second macro I run is a "Clear Day" macro, which should essentially reset the day / clear all the cell contents and validations / clear all formatting, etc. It also uses an INTERSECT technique to delete images/checkboxes (see code below). If I do not use data validation, this all works swimmingly - zero errors. However, if I use data validation, it breaks the macro.

The macro actually still accomplishes everything it's designed to do, and the Intersect method works just fine to delete all the objects, but I get an error:


1004 - Application-defined or Object-defined Error


... which then returns to the macro code, and highlights the INTERSECT line. Again, the macro does everything it needs to (objects get deleted, cells get completely reset, etc.). But this error is preventing me from feasibly using dropdowns/lists without a lot of headache.


Any ideas?


Weird things:

• The problem only happens when I actively select the dropdown list. If I copy/paste a dropdown list but never select it, my "MsnClearDay" macro still works without errors
• The problem also happens even if I run a "Range.Validation.Delete" command, or "Range.Clear / Range.ClearContents."
• The problem does NOT happen if I delete all the cells and repopulate them. I ran macros to delete all those cells, and re-insert fresh cells, and it remedies the problem, allowing me to run my "Delete Objects" portion of my macro.

^ Because of that last point, I suspect there's some kind of 'cell type' or other characteristic/property being applied to a cell as soon as I select it and it displays as a dropdown/list... but my forensics haven't been able to figure it out. I'm absolutely puzzled.


Code is below

<code>Sub MsnClearDay()
    'Clears a full day; user must select the upper left-most cell for this macro to work properly

    'Confirms user wants to execute the function
        If MsgBox("This will erase the current day! Are you sure?", vbYesNo) = vbNo Then Exit Sub

    ActiveCell.UnMerge
    Range(ActiveCell, ActiveCell.Offset(19, 9)).Clear
    With Range(ActiveCell, ActiveCell.Offset(19, 9)).Validation
        .Delete
    End With

'This part deletes objects (checkboxes/images)

    Dim s As Shape
    Set r = Worksheets("DET 2 Whiteboard").Range(ActiveCell, ActiveCell.Offset(19, 9))

    For Each s In ActiveSheet.Shapes
        If Not Intersect(r, s.TopLeftCell) Is Nothing Then
            s.Delete
        End If
    Next s

    End Sub

Any help would be amazing! Thanks everyone for the time!
- Starfox C17
</code>

 

[JLGWhiz]

For Each s In ActiveSheet.Shapes
        If Not Intersect(r, s.TopLeftCell) Is Nothing Then
            s.Delete
        End If
    Next s

If all you want to do is delete all the shapes then use this instead

For i = ActiveSheet.Shapes.Count To 1 Step -1
 ActiveSheet.Shapes(i).Delete
Next

 

[StarfoxC17]

Thanks for the reply! I'm actually trying to delete shapes/objects within a certain range of cells, which is why I have to use the INTERSECT method. Unless there's another way to delete objects within a range that I haven't seen. But this is the only one searching/google would turn up for me

 

[JoeMo]

Have you tried adding an error redirect like this?

Sub MsnClearDay()
    'Clears a full day; user must select the upper left-most cell for this macro to work properly

    'Confirms user wants to execute the function
        If MsgBox("This will erase the current day! Are you sure?", vbYesNo) = vbNo Then Exit Sub
Dim s As Shape, r As Range
    ActiveCell.UnMerge
    Set r = Range(ActiveCell, ActiveCell.Offset(19, 9))
    With r
        .Clear
        .Validation.Delete
        For Each s In ActiveSheet.Shapes
            On Error Resume Next
            If Not Intersect(.Cells, s.TopLeftCell) Is Nothing Then
                s.Delete
            End If
        Next s
    End With
End Sub

 

[StarfoxC17]

Had no idea error redirects were a thing! This worked really well - left one little quirk that I have to "save" for the validation to appear again, but that's super minor compared to the headache it was giving me earlier. Thanks a bunch!

Way to clean up my code too I'm working on it hah.

- Starfox C17

 

[JoeMo]

Had no idea error redirects were a thing! This worked really well - left one little quirk that I have to "save" for the validation to appear again, but that's super minor compared to the headache it was giving me earlier. Thanks a bunch!

Way to clean up my code too I'm working on it hah.

- Starfox C17

You are welcome - thanks for the reply.