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!

--
Steve C

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


On Mar 15, 3:07 pm, Steve C wrote:
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!

--
Steve C

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

--
Steve C


"Susan" wrote:

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


On Mar 15, 3:07 pm, Steve C wrote:
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!

--
Steve C

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



On Mar 15, 3:57 pm, Steve C wrote:
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

--
Steve C



"Susan" wrote:
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

On Mar 15, 3:07 pm, Steve C wrote:
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!

--
Steve C- Hide quoted text -

- Show quoted text -

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

Steve C wrote:

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!

--
Steve C

--

Dave Peterson

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


"Dave Peterson" wrote:

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

Steve C wrote:

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!

--
Steve C

--

Dave Peterson