Using a Macro to clear the box

I have a great little WS that Professorjimjam helped me with that I'm using to build out a list of statements and then have a "check box" indented capability in Cell C,  D and E with Notes in Cell K.  I now need a macro that, based on the helper number in cell A (1,2 or 3), will clear the cell contents in Cells C, D and E.  That is 1=Clear C, 2=Clear D and 3 is Clear E.  The Notes in Cell K also need to be cleared.  If the helper cell is =0 or is "", then no cell need be cleared on that row (this is so I can add rows later and simply flip the helper cell to active by using a 1, 2 or 3.

Here is the worksheet.

And....as always, "thank you" ,,..... in advance!


B.
Clearing-the-Boxesv1.xlsm
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Sub ClearCheckBox()
Set Rng = Range(Range("A5"), Range("A" & Cells.Rows.Count).End(xlUp))
For Each c In Rng
    If c <> 0 Then c.Offset(0, c.Value + 1) = ""
Next

End Sub

Open in new window

Regards
Bright01Author Commented:
I get a "Rng variable not defined" error.

B.
Martin LissOlder than dirtCommented:
This also clears column K. Note that changing the values on the sheet normally triggers your Worksheet_Change event code and I added lines 7 and 16 to prevent that. If you want to have the Change event code fire then remove those two lines.

Sub ClearText()
Dim lngLastRow As Long
Dim lngRow As Long

With ActiveSheet
    lngLastRow = .Range("A1048576").End(xlUp).Row
    Application.EnableEvents = False
    
    For lngRow = 5 To lngLastRow
        If .Cells(lngRow, "A") > 0 Then
            .Cells(lngRow, .Cells(lngRow, "A") + 3) = ""
            .Cells(lngRow, "K") = ""
        End If
    Next
    
    Application.EnableEvents = True
    
End With
End Sub

Open in new window

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Martin LissOlder than dirtCommented:
If you want to use Rgonzo1971's code you'll need to add Dim Rng As Range between his lines 1 and 2.
Bright01Author Commented:
Martin,

Greetings!  Can you take one more look/pass at your code?  It doesn't clear the check boxes.... it clears all the text.

The Text represent statements that I want to preserve to reuse.  The check boxes and column K are what I'm looking to actually clear.

So when you fire the macro, all that gets cleared are the check boxes (reset to blank) and Col. K Comments/Notes.

Thanks much,

B.
Martin LissOlder than dirtCommented:
Sorry I misunderstood.

Sub ClearCheckboxes()
Dim lngLastRow As Long
Dim lngRow As Long

With ActiveSheet
    lngLastRow = .Range("A1048576").End(xlUp).Row
    Application.EnableEvents = False
    
    For lngRow = 5 To lngLastRow
        If .Cells(lngRow, "A") > 0 Then
            .Cells(lngRow, .Cells(lngRow, "A") + 2) = ""
            .Cells(lngRow, "K") = ""
        End If
    Next
    
    Application.EnableEvents = True
    
End With

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Perfect!  Clears it right up and works well.

Much thanks to both of you.



B.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.
I like the little trick used to create the "checkboxes".

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
Bright01Author Commented:
So Martin,  you want to get something really cool going with the check boxes?  Let's add three levels of checks.  Click once and you get a Check. Click again and it rotates to a Question Mark.  Click a third time and you get an X.  This way you can relate the status as well as the fact that the statement has been or is being addressed.  I think that's a simple add to the existing code but don't know how to place it or to make sure "the cycle" works.

Do I need to author another question?

Gotta love the technology and what you can do with it !!!

B.
Martin LissOlder than dirtCommented:
I don't think that's possible since Excel doesn't supply a cell Click event. You can tell when the cell is selected and do something like display a checkmark when that happens, but to change it to a question mark you'd have to select a different cell and then select the first one again in order to effect the change.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.