How do I check/uncheck ten or odd Checkboxes by click on one check

Is it posible to use command button to check/uncheck all the Checkboxes in
the spread sheet? Please help, thanks!

Hello Ken,

Here are the macros to accomplish your tasks. Add a VBA Module to your project and copy this code into it. You can assign "ClearCheckboxes" to one command button and "CheckCheckboxes" to another.

Macro Code:
_____________________________

Sub ClearCheckBoxes()

Dim Shp

For Each Shp In ActiveSheet.Shapes
X = Shp.Type
If X = msoFormControl Then
If Shp.FormControlType = xlCheckBox Then
Shp.ControlFormat.Value = False
End If
End If
Next Shp

End Sub

Sub CheckCheckBoxes()

Dim Shp

For Each Shp In ActiveSheet.Shapes
X = Shp.Type
If X = msoFormControl Then
If Shp.FormControlType = xlCheckBox Then
Shp.ControlFormat.Value = True
End If
End If
Next Shp

End Sub
_____________________________

Sincerely,
Leith Ross

Hi Leith,
Thanks for your help but I still can't get it working. What i did was
created numerous of checkboxes and then a command button trhough "Control
Toolbar". Than I click view codes and entered the codes that you had
provided. It didn't do anything. Am i doing something wrong? Please help.
Thanks!

"Leith Ross" wrote:

>
> Hello Ken,
>
> Here are the macros to accomplish your tasks. Add a VBA Module to your
> project and copy this code into it. You can assign "ClearCheckboxes" to
> one command button and "CheckCheckboxes" to another.
>
> MACRO CODE:
> _____________________________
>
> Sub ClearCheckBoxes()
>
> Dim Shp
>
> For Each Shp In ActiveSheet.Shapes
> X = Shp.Type
> If X = msoFormControl Then
> If Shp.FormControlType = xlCheckBox Then
> Shp.ControlFormat.Value = False
> End If
> End If
> Next Shp
>
> End Sub
>
> Sub CheckCheckBoxes()
>
> Dim Shp
>
> For Each Shp In ActiveSheet.Shapes
> X = Shp.Type
> If X = msoFormControl Then
> If Shp.FormControlType = xlCheckBox Then
> Shp.ControlFormat.Value = True
> End If
> End If
> Next Shp
>
> End Sub
> _____________________________
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=497742
>
>

This is the code I have for clearing Checkboxes:
____________________________________________________________________

Dim ChkBoxId As String

ChkBoxId = "Forms.CheckBox.1"

With ActiveSheet
For I = 1 To .OLEObjects.Count
If .OLEObjects(I).progID = ChkBoxId Then
.OLEObjects(I).Object.Value = False
End If
Next I
End With
_____________________________________________________________________

But how do I do the opposite? What's the code for it? Please help. Thanks!

"Ken Vo" wrote:

> Hi Leith,
> Thanks for your help but I still can't get it working. What i did was
> created numerous of checkboxes and then a command button trhough "Control
> Toolbar". Than I click view codes and entered the codes that you had
> provided. It didn't do anything. Am i doing something wrong? Please help.
> Thanks!
>
> "Leith Ross" wrote:
>
> >
> > Hello Ken,
> >
> > Here are the macros to accomplish your tasks. Add a VBA Module to your
> > project and copy this code into it. You can assign "ClearCheckboxes" to
> > one command button and "CheckCheckboxes" to another.
> >
> > MACRO CODE:
> > _____________________________
> >
> > Sub ClearCheckBoxes()
> >
> > Dim Shp
> >
> > For Each Shp In ActiveSheet.Shapes
> > X = Shp.Type
> > If X = msoFormControl Then
> > If Shp.FormControlType = xlCheckBox Then
> > Shp.ControlFormat.Value = False
> > End If
> > End If
> > Next Shp
> >
> > End Sub
> >
> > Sub CheckCheckBoxes()
> >
> > Dim Shp
> >
> > For Each Shp In ActiveSheet.Shapes
> > X = Shp.Type
> > If X = msoFormControl Then
> > If Shp.FormControlType = xlCheckBox Then
> > Shp.ControlFormat.Value = True
> > End If
> > End If
> > Next Shp
> >
> > End Sub
> > _____________________________
> >
> > Sincerely,
> > Leith Ross
> >
> >
> > --
> > Leith Ross
> > ------------------------------------------------------------------------
> > Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> > View this thread: http://www.excelforum.com/showthread...hreadid=497742
> >
> >

Hello Ken,

This is a good example of the devil being in the details. There are 2 ways to create controls on a worksheet. One is by using the FORMS toolbar and the other is the CONTROL TOOLBOX. I had thought about including examples using both types since the are very different at the code level. The code I wrote was for the FORMS type which people use most often with worksheets. The CONTROL TOOLBOX is generally used on VBA UserForms.

The behavior of either type of CheckBox is the same. If it is checked it's value property is TRUE and if it clear then the value property is FALSE. To check the checkbox...

If .OLEObjects(I).progID = ChkBoxId Then
.OLEObjects(I).Object.Value = TRUE
End If

Sincerely,
Leith Ross

Thanks Leigh!

I appreciate your effort and time. It worked perfectly.

"Leith Ross" wrote:

>
> Hello Ken,
>
> This is a good example of the devil being in the details. There are 2
> ways to create controls on a worksheet. One is by using the FORMS
> toolbar and the other is the CONTROL TOOLBOX. I had thought about
> including examples using both types since the are very different at the
> code level. The code I wrote was for the FORMS type which people use
> most often with worksheets. The CONTROL TOOLBOX is generally used on
> VBA UserForms.
>
> The behavior of either type of CheckBox is the same. If it is checked
> it's value property is TRUE and if it clear then the value property is
> FALSE. To check the checkbox...
>
> If .OLEObjects(I).progID = ChkBoxId Then
> .OLEObjects(I).Object.Value = TRUE
> End If
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=497742
>
>