Delete Check Boxes and Entire Row

[SaraWitch]

Hello all,

Does anyone know how to also delete check boxes when deleting a row? (I don't want all check boxes on sheet deleted, just those in the row that has been deleted.)

Ta muchly!

 

[Marc L]

Hi,​

​

on a smart worksheet the easy way is first to well set the CopyObjectsWithCells property as explained in VBA help …​

 

[SaraWitch]

Thanks, @*Marc L. Forgive my ignorance; so, do I just use "Application.CopyObjectsWithCells = True" in a standard module? (Also not sure where 'VBA help' is...)

 

[Marc L]

​

Yes and if the worksheet is well designed, if the checkbox belongs only to the row to be deleted …​

​

But as initial post is a bit unclear : is it for manual deletion rather than with a VBA procedure ?​

​

 

[SaraWitch]

Sorry; I should have been clearer. So I have a sheet with several rows, most of which contain checkboxes. This is a template, and users may need to delete lines, which will be a manual delete (right click row>delete). However, whilst the row deletes, the checkboxes do not and the sheet ends up with several checkboxes on top of each other as a result. I would like the checkboxes to delete when manually deleting the row.

I hope this is clearer...

p.s. I put the "Application.CopyObjectsWithCells = True" into a Standard Module, but the checkboxes remain when the row is manually deleted.

 

[Marc L]

So first add this event code to the ThisWorkbook module rather than in any standard module :​

Private Sub Workbook_Open()
    Application.CopyObjectsWithCells = True
End Sub

Once done save the workbook, close it, reopened it and try to delete a 'check box row' …​

 

[mikerickson]

Have you looked at Marlett checkboxes. Where a cell itself becomes a check box.
Put this code into a sheet's code module and double clicking a cell in column D will make it act like a check box.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target
        If .Column = 4 Then
            Cancel = True
            If .Value = "a" Then
                .Value = vbNullString
            Else
                .Value = "a"
                .Font.Name = "Marlett"
            End If
        End If
    End With
End Sub

There are variations on this idea (single click instead of double) and you could replace your checkboxes by making their linked cells Marlett checkboxes. No need to deal with both objects and cells.

 

[SaraWitch]

Thank you both.

@Marc L, this isn't working for me. When I manually delete a row, the checkboxes still remain...

@mikerickson, I really like your suggestion. However, I also have a DDL in the same cell, so do need the checkbox (this workbook is a bit of a monster and I know I could add/merge another column to accommodate this, but the formatting fits with data above, and to change it all would be a bit of a headache!). I will keep this in mind for simpler sheets though; thank you.

It would be good if there was a really simple formatting option to be able to delete everything in a row!

 

[Marc L]

​

Yes a dumb way is to loop on any object and when its cell address match the row so to be deleted as well …​

To avoid a never ending guessing story an attachment to insvestigate in is the better to help …​

 

[SaraWitch]

Here is a link to the workbook (I didn't attach it before as I didn't think it would be needed). An example page is Sheet 16 (Quarterly Tasks - Pull Cords) in the 'Property Pull Cords' section.

Compliance Check Log.xlsm

1drv.ms

I just want to be able to delete entire row and content, including checkboxes. Or even if there is a way to quickly select the checkboxes to delete (I know Ctrl A, but this selects all objects on Sheet and I only want to select a few!).