Clear Entire Row if Checkbox Selected

[SarahTheBookDragon]

Howdy!
I have an excel sheet that has a table spanning from Columns A-L.
I have a row of checkboxes in Column M
The checkboxes have conditional formatting that grays/strikes out the corresponding row that it is associated with

However- I want to update this so that it clears the row entirely.
Please note: I only want the row to be clear. I do not want the row to be deleted (my hope is to continue to use the same worksheet without having to re-add the checkboxes) .

I found a VBA code here that I have used with some success:

Public Sub CheckBox()
If Range("M18") = True Then
Range("A18:L18").ClearContents
End If

However- I want to apply this to all the checkboxes in Column M.

 

[rollis13]

Have a Try. My macro, as it is, will test all the checkboxes in the sheet, so if you have other checkboxes throughout the sheet other than column M, you'll need to narrow down the range being tested (as homework ).

Option Explicit
Public Sub CheckBox()
    Dim ChkBox As Object
    Dim CellRow As String
    For Each ChkBox In ActiveSheet.CheckBoxes       'loop all through checkboxes
        If ChkBox.Value = 1 Then                    'detect if checkbox is checked
            CellRow = ChkBox.TopLeftCell.Row        'detect checked checkbox row
            Range("A" & CellRow & ":L" & CellRow).ClearContents 'clear cells
            ChkBox.Value = 0                        'reset checkbox
        End If
    Next ChkBox
End Sub

 

[kevin9999]

Possible alternative - only checks checkboxes that change in column M. Change sheet name to suit.

Sub Change_Check_Boxes()
    Dim c As Excel.CheckBox
    Set c = Worksheets("Sheet1").CheckBoxes(Application.Caller) '<-- *** Change sheet name to suit ***
    If c.TopLeftCell.Column = 13 Then
        If c.Value = xlOn Then Range(Cells(c.TopLeftCell.Row, 1), Cells(c.TopLeftCell.Row, 12)).ClearContents
    End If
End Sub

 

[SarahTheBookDragon]

Have a Try. My macro, as it is, will test all the checkboxes in the sheet, so if you have other checkboxes throughout the sheet other than column M, you'll need to narrow down the range being tested (as homework ).

Option Explicit
Public Sub CheckBox()
    Dim ChkBox As Object
    Dim CellRow As String
    For Each ChkBox In ActiveSheet.CheckBoxes       'loop all through checkboxes
        If ChkBox.Value = 1 Then                    'detect if checkbox is checked
            CellRow = ChkBox.TopLeftCell.Row        'detect checked checkbox row
            Range("A" & CellRow & ":L" & CellRow).ClearContents 'clear cells
            ChkBox.Value = 0                        'reset checkbox
        End If
    Next ChkBox
End Sub

Thank you for your reply - this definitely did something... but did not work as i hoped
It cleared other rows with checkboxes (but not the UNSELECTED ones)

 

[rollis13]

It cleared other rows with checkboxes (but not the UNSELECTED ones)

At least it clears out only selected checkboxes .
In the absence of a sample file, since we don't have a crystal ball, you'd mind explaining exactly what your hope was and whether, as I pointed out, you have any other checkboxes throughout your sheet besides the ones in column M.

 

[kevin9999]

Did you try the code in post #3?