# Clear row when a checkbox is checked



## MathWeis (Dec 29, 2022)

Hey! I'm completely new to VBA, so I have no clue where I should begin. I thought I'd air it out here, any help is greatly appreciated .

Short and simple:
- When I check a box in cell(I3), the entire row is cleared (A3:H3) and the box is unchecked.

I've tried a couple of different codes I've taken from online, but nothing seems to be working.. 

Again, any help is greatly appreciated!


----------



## jdellasala (Dec 29, 2022)

VBA. Bahh HUMBUG!
Book1AB1NameAttended2TalanFALSE3TRUE4LinaFALSE5TRUE6AbigayleFALSESheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueA2:A6Expression=$B2=TRUEtextNO
You'll have to believe me that there are checkboxes in Column B! I made their status visible.
Rows in Column A use conditional formatting to change the cell's format from General (or whatever) to a Custom format of ";;;" - 3 semicolons without the quotes. That effectively hides any value in the cell (Positive*;*Negative*;*Zero*;*Text).
You could also change the Font Color to whatever the cell's background color is.
So it's unclear in your question whether the VALUES in the cells need to be cleared, but it sounded like not. You would have to go to VBA for that, but instinctively unless the data to be cleared is coming from some other source like an XLOOKUP, I kind of freak when I hear clearing data that has no other source or backup. But that's me!


----------



## MathWeis (Dec 29, 2022)

jdellasala said:


> VBA. Bahh HUMBUG!
> Book1AB1NameAttended2TalanFALSE3TRUE4LinaFALSE5TRUE6AbigayleFALSESheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueA2:A6Expression=$B2=TRUEtextNO
> You'll have to believe me that there are checkboxes in Column B! I made their status visible.
> Rows in Column A use conditional formatting to change the cell's format from General (or whatever) to a Custom format of ";;;" - 3 semicolons without the quotes. That effectively hides any value in the cell (Positive*;*Negative*;*Zero*;*Text).
> ...


I'm creating a system for products with expiration dates, so the data itself has to be cleared and not hidden:/. The information itself is not that important to be honest, I've made a sorting system that highlights different ranges of expiration dates with colors that indicates a level of priority. After the employee has fixed the issue with an item, they should just be able to remove the line. They can easily do that by deleting the content of course, but there are a lot of older people that would never take the time to do so..


----------



## John_w (Dec 29, 2022)

MathWeis said:


> - When I check a box in cell(I3), the entire row is cleared (A3:H3) and the box is unchecked.


For a form control checkbox, assign this to the checkbox.

```
Public Sub CheckBox_Click()
    Range("A3:H3").ClearContents
    ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value = 0
End Sub
```


----------



## MathWeis2 (Dec 30, 2022)

John_w said:


> For a form control checkbox, assign this to the checkbox.
> 
> ```
> Public Sub CheckBox_Click()
> ...


That worked wonderfully! If I may add: how do I edit the code so that the checkbox is assigned to its row. Example: clicking the box in I3 clears A3:H3, clicking the box in I4 clears A4:H3 and so on until the last line (I've capped the document at 300, so the last line to be cleared would be A300:H300 when I300 is clicked).


----------



## bebo021999 (Dec 30, 2022)

So you have to insert 300 checkboxes, haven't you?
Why not alter way: double click on cell, i.e, I3 then A3:H3 will be cleared?


----------



## shinigamilight (Dec 30, 2022)

MathWeis2 said:


> That worked wonderfully! If I may add: how do I edit the code so that the checkbox is assigned to its row. Example: clicking the box in I3 clears A3:H3, clicking the box in I4 clears A4:H3 and so on until the last line (I've capped the document at 300, so the last line to be cleared would be A300:H300 when I300 is clicked).




```
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
       
        If Intersect(Target, Range("I:I")) Is Nothing = False Then
            Range("A" & Target.Row, Target.Offset(0, 1)).Clear
        End If
End Sub
```
Use something like this that's better than messing around with checkboxes. Each time you double click in the I column everything to its left will be cleared.


----------



## John_w (Dec 30, 2022)

MathWeis2 said:


> how do I edit the code so that the checkbox is assigned to its row. Example: clicking the box in I3 clears A3:H3, clicking the box in I4 clears A4:H3 and so on until the last line (I've capped the document at 300, so the last line to be cleared would be A300:H300 when I300 is clicked).


It's not clear to me from your OP and the above quote whether you have one checkbox or multiple checkboxes.

Whether you have one or multiple checkboxes, this clears columns A:H on the same row as the checkbox:

```
Public Sub CheckBox_Click()
    With ActiveSheet.Shapes(Application.Caller)
        Range("A" & .TopLeftCell.Row & ":H" & .TopLeftCell.Row).ClearContents
        .OLEFormat.Object.Value = 0
    End With
End Sub
```

And you can run the following macro to assign the CheckBox_Click macro to every checkbox, to save you having to do so manually.

```
Public Sub Set_CheckBoxes_OnAction()
    Dim i As Long
    With ActiveSheet
        For i = 1 To .CheckBoxes.Count
            .CheckBoxes(i).OnAction = "CheckBox_Click"
        Next
    End With
End Sub
```


----------

