VBA to clear all CheckBoxes?

  • Thread starter Thread starter newboy18
  • Start date Start date
N

newboy18

Please help, I wanted to create a small loop to clear all
checkboxes on a sheet and I thought I could do something
like this:

dim cb as object
set cb=sheet1.checkboxes
for each cb
cb.value=false
next cb
set cb=nothing

What have I done wrong?
 
If you're using Forms toolbar checkboxes you can do this:

Sheet1.Checkboxes.Value = xloff

If they're from the Control Toolbox:

Sub TurnOff()
Dim X As OLEObject
For Each X In Sheet1.OLEObjects
If X.progID = "Forms.CheckBox.1" Then
X.Object.Value = False
End If
Next
End Sub


--
Jim Rech
Excel MVP
| Please help, I wanted to create a small loop to clear all
| checkboxes on a sheet and I thought I could do something
| like this:
|
| dim cb as object
| set cb=sheet1.checkboxes
| for each cb
| cb.value=false
| next cb
| set cb=nothing
|
| What have I done wrong?
 
Sub ClrChks()
Dim s As Shape, cb As CheckBox
For Each s In Sheet1.Shapes
If s.Type = msoFormControl And s.FormControlType = xlCheckBox Then
Set cb = s.DrawingObject
cb.Value = 0
End If
Next
End Sub
 
Thanks Jim, yes they were from the Control Toolbox.
I have not seen prodID before but it works fine.
 
Back
Top