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


"Thomas M." wrote:

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