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]

​

According to your attachment, for any row manually deleted I can't see any way except the one in post #7 (no object so no issue !)​

maybe someone else may find a trick …​

​

Deleting row manually can be forbidden with a sheet protected then with a button to delete a row it could be done via a VBA procedure.​

​

 

[Marc L]

​

If nobody find any trick, if you wanna stay absolutely with the actual check boxes design and still with the manual row deletion,​

I see some clunky way but as your workbook is a bit heavy …​

The idea is to use some VBA event, like when the worksheet is activated or desactivated or when the workbook is saved whatever,​

to find out for some 'ghost check boxes' by comparing their cell addresses and if they are behind other check boxes …​

Maybe someone else may expose a better idea …​

 

[Jaafar Tribak]

Which type of CheckBoxes are we talking about ? Forms checkboxes or ActiveX checkboxes ?

Also, is tis applicable to only one sheet or to all sheets ?

 

[SaraWitch]

Hi @JAAFAR Tribac.

Checkboxes in this workbook are Forms checkboxes (but ActiveX checkboxes also do not delete with row (I've tried both!)).

I have found a small workaround when setting up templates (I need to set up around 20) - I deleted all the checkboxes and put fewer rows in the Master, then I'm copying and pasting rows as needed in individual workbooks. However, there will be occasions when the user will have to delete rows with checkboxes themselves and this is where the issue is. Some sheets will end up really messy with loads of checkboxes on top of each other!

It's happening on all sheets in workbook, and I even created a blank test workbook and they don't delete in that either! I have looked at formatting conditions, Find & Select Select Object/Selection Pane (you still have to select checkboxes one by one here too!), but no joy.

 

[mikerickson]

​

If nobody find any trick, if you wanna stay absolutely with the actual check boxes design and still with the manual row deletion,​

I see some clunky way but as your workbook is a bit heavy …​

The idea is to use some VBA event, like when the worksheet is activated or desactivated or when the workbook is saved whatever,​

to find out for some 'ghost check boxes' by comparing their cell addresses and if they are behind other check boxes …​

Maybe someone else may expose a better idea …​

This code, when put in the sheet's code module, will detect when a row is deleted. If there is a whole column reference formula, like =COUNTA(E:E) somewhere.

' in a sheet's code module

Dim lastRow As Range

Private Sub Worksheet_Calculate()
    On Error GoTo Out
    If lastRow.Rows.Count > 1 Then
        MsgBox "row " & Selection.Row & " was deleted"
    End If
Out:
    Rem row inserted
    Set lastRow = Rows(Rows.Count)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set lastRow = Rows(Rows.Count)
End Sub

 

[Jaafar Tribak]

The following code relies on the CheckBoxes TopLeftCell Property so it will only work properly if the checkboxes are properly located inside their repective rows. (To check if the checboxes are properly placed, rightclick to select them and see if the bounding box with the selection grip is within the correct row)

Place this code in the ThisWorkbook Module:

Option Explicit

Private oCol As Collection, SearchRange As Range, lSearchRangeRowsCount As Long

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    Dim oChBx As CheckBox, oCell As Range
    Dim lPrevLRW As Long, lPrevLRC As Long, llastRow As Long, llastCol As Long

    For Each oChBx In Sh.CheckBoxes
        llastRow = Application.Max(oChBx.TopLeftCell.Row, lPrevLRW)
        llastCol = Application.Max(oChBx.TopLeftCell.Column, lPrevLRC)
        lPrevLRW = llastRow
        lPrevLRC = llastCol
    Next

    Set SearchRange = Sh.Range(Cells(1, 1), Cells(llastRow + 1, llastCol + 1))
    lSearchRangeRowsCount = SearchRange.Rows.Count
    Set oCol = New Collection
    On Error Resume Next
    For Each oCell In Intersect(SearchRange, Target.EntireRow).Cells
        For Each oChBx In Sh.CheckBoxes
            If oChBx.TopLeftCell.Row = oCell.Row Then
                oCol.Add oChBx, CStr(oChBx.Name)
            End If
        Next oChBx
    Next oCell
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim oChBx As CheckBox
    
    If SearchRange.Rows.Count < lSearchRangeRowsCount Then
        For Each oChBx In oCol
            oChBx.Delete
        Next oChBx
    End If

End Sub

 

[Marc L]

​

As the workbook seems heavy according to its behavior on my side I was thinking about using another event …​

 

[Marc L]

​

Sara, do agree with the post #15 to add some 'fake' formula within each worksheet where exist check boxes​

​

As this cell formula can be 'hidden' behind an existing text box or applying a white font color or just custom formatting the cell as ';;;' …​

 

[SaraWitch]

Thank you everyone, but nothing is working for me (Jaafar, your code was making Excel not respond and I had to keep ending the programme in Task Manager! ).

I've now set up all templates with minimal checkboxes and just hope that users won't have to delete too many rows!

Frustrating that Excel doesn't just delete everything in that row (the checkboxes are definitely located within rows). ?

 

[Marc L]

As I had the same issue with post #16, I investigated from post #15 trick which works but under condition …​

Ideally the best is to not use any check box ! Following post #7 avoids some gas factory code to manage check boxes …​

​

If really wanna use check boxes :​

• following post #15, it well works if all the check boxes are created according to the rows order which is not the case
  with some worksheets, I guess some rows was inserted after previous rows created …
  So the workaround is to reorder each check box according to their row # via the ZOrder method : a gas factory code may start here
  as when a row is deleted each check box must be compared to another with the same TopLeftCell address according to their ZOrderPosition …
  
  
• Another way leads to protect the worksheet in order any row can be inserted or deleted manually but via buttons launching VBA procedures …
  
  
• Another way is to have an unique row ID, could be hidden in a column like column A for example and storing this ID in a property of each check box.
  So when a row is deleted, the idea is to check if the ID stored in the check box stil exists but this way needs also to detect if a row is inserted or copied …