Clear All Check Boxes

[Guest]

I have a sheet of check boxes created using the Control Toolbox toolbar that
displays magazine subscriptions for employees. I'm looking for a command that
will automatically clear all the checkboxes on that sheet upon opening the
workbook. Rather than set each checkbox value to false, I'm sure there's an
easier way. Thanks!

 

[Susan]

well, that's what you have to do, but you can do it quickly &
easily...........
call this routine from an auto-open sub......

Private Sub change_the_value()

Dim oControl As OLEObject
Dim ws As Worksheet

Set ws = ActiveSheet

For Each oControl In ws.OLEObjects

If TypeName(oControl.Object) = "Checkbox" Then
With oControl
.Value = False
End With

End If

Next oControl

End Sub

hope it works!
susan

 

[Guest]

Hi, Susan:

The code wasn't working (when I stepped through it, it didn't recognize the
If test and kept looping). I did a slight modification, using "CheckBox"
instead of "Checkbox" and that helped.

However, when it gets to the line ".value = False", I get an error message
"Object doesn't support this property or method." Any other tweaks I can
make to the code that might help? Thanks! (below is my current code from
what you gave me):

Dim ws As Worksheet
Set ws = ActiveSheet

For Each oControl In ws.OLEObjects
' If TypeOf oControl.Object Is CheckBox Then
If TypeName(oControl.Object) = "CheckBox" Then
With oControl
.Value = False
End With
End If
Next oControl

 

[Susan]

this was a quick modification of a sub i used for option buttons & i
didn't test it, so that's why i didn't get the CheckBox thing.

the other modification was changing
..Property
to
..Value
which i assumed would work fine, & apparently doesn't!

instead of

With oControl
.Value = False
End With

maybe try taking out the with........

ocontrol.value=false

or maybe you have to make it

set ocontrol.value=false

(i always get it mixed up if something's an object or not, so that's
one of the first fixes i try - either adding "set" or taking it out).
otherwise i don't know why it's erroring on that........
:/
sorry!
susan

 

[Dave Peterson]

Option Explicit
Sub auto_open()
Dim wks As Worksheet
Dim OLEObj As OLEObject

Set wks = Worksheets("sheet1")

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Sub

 

[Guest]

Thanks, Dave. Your code did the trick. Thanks, Susan, for your help as well!