Clear row when a checkbox is checked

[MathWeis]

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]

VBA. Bahh HUMBUG!

Book1
	A	B
1	Name	Attended
2	Talan	FALSE
3		TRUE
4	Lina	FALSE
5		TRUE
6	Abigayle	FALSE
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A6	Expression	=$B2=TRUE	text	NO

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]

VBA. Bahh HUMBUG!

Book1
	A	B
1	Name	Attended
2	Talan	FALSE
3		TRUE
4	Lina	FALSE
5		TRUE
6	Abigayle	FALSE
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A6	Expression	=$B2=TRUE	text	NO

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!

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]

- 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]

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

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]

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]

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]

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