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 <Ste...@discussions.microsoft.com> 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 <Ste...@discussions.microsoft.com> 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 <Ste...@discussions.microsoft.com> 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 <Ste...@discussions.microsoft.com> 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
>