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
Here is the worksheet.
And....as always, "thank you" ,,..... in advance!
B.
Clearing-the-Boxesv1.xlsm
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
If you want to use Rgonzo1971's code you'll need to add Dim Rng As Range between his lines 1 and 2.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Clears it right up and works well.
Much thanks to both of you.
B.
Much thanks to both of you.
B.
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
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
ASKER
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.
Do I need to author another question?
Gotta love the technology and what you can do with it !!!
B.
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.
ASKER
B.