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

K

Ken Vo

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

Leith Ross

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
 
K

Ken Vo

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!
 
K

Ken Vo

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!
 
L

Leith Ross

Hello Ken,

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

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

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

Sincerely,
Leith Ros
 
Top