Working With Checkboxes

[Thomas M.]

Excel 2007

I am building a spreadsheet that is essentially a checklist. Currently, on
each line item we just type an "x" if that item needs to be configured.
What I would like to do is put checkboxes on each line item, and then add
Check All and Clear All checkboxes at the top of the list.

I did something like this a couple of years ago in Excel 2003, and I seem to
remember that it required code for the Check All and Clear All checkboxes
that would enumberate all the checkboxes in the list and then loop through
and set each checkbox. I could probably set that up again, but the problem
is that we don't know what will be added or removed from the list in the
future, and I would like things setup in such a way that anyone modifying
the spreadsheet in the future will not need to edit the programming.

How can I setup this functionality so that the macro will work--without any
editing--no matter what gets added or deleted in the future?

--Tom

 

[Barb Reinhardt]

Try something like this. Of course, this assumes that the check boxes aren't
renamed.

Option Explicit

Sub ClearCheckBox()

Dim myWS As Excel.Worksheet
Dim myShape As Excel.Shape

Set myWS = ActiveSheet
For Each myShape In myWS.Shapes
Debug.Print myShape.Name,
Debug.Print myShape.ControlFormat.Value


If myShape.Name Like "Check*" Then

myShape.ControlFormat.Value = -4146

End If
Next myShape
End Sub

 

[Rick Rothstein]

Why would you want to add all those extra controls for? You can stay with
your current X system and use the following macros (which you can assign to
Buttons) to clear and check the column all at once. You didn't tells us
anything about your worksheet's structure, so I guessed that Column A is
where you put the X's and Column B contains the descriptions for what is
being checked and unchecked.

Sub CheckAll()
Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row).Value = "X"
End Sub

Sub ClearAll()
Range("A1:A" & Cells(Rows.Count, "B").End(xlUp).Row).Value = ""
End Sub

If you want to simplify the process of checking and unchecking the Column A
cells, you can use this event code to make a double-click toggle the X's on
and off...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim LastRow As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
If Target.Column = 1 And Target.Row <= LastRow Then
Cancel = True
If Target.Value = "" Then
Target.Value = "X"
Else
Target.Value = ""
End If
End If
End Sub

To install this event code (the two macros should go in Modules),
right-click the tab at the bottom of the sheet that you want to have this
functionality, select View Code from the popup menu that appears and then
Copy/Paste the event procedure into the code window that appeared. Now, when
you double click a cell in Column A that is on a row equal to or less than
the last row of data in Column B, the cell will toggle between an X and no
X.