Please help, I have designed a simple spreadsheet that
uses Check Boxes and Option Buttons from the Control
Toolbox and I am trying to make a small VBA routine to
clear them all.
I thought I could use:

Sub Clear_cb_ob()
Dim cb As CheckBox
Dim ob As OptionButton
For Each cb In Sheet1
cb.Value = False
Next
For Each ob In Sheet1
ob.Clear
Next
End Sub

But I just get:
Object doesn't support this property or method

Sub ClearCheckboxes()
Dim i As Long

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "CheckBox" Then
ActiveSheet.OLEObjects(i).Object.Value = False
End If
Next i

For i = 1 To ActiveSheet.OLEObjects.Count
If TypeName(ActiveSheet.OLEObjects(i).Object) = "OptionButton" Then
ActiveSheet.OLEObjects(i).Object.Value = False
End If
Next i

End Sub




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"newboy18" <(E-Mail Removed)> wrote in message
news:21db301c45b06$6aeb3f70$(E-Mail Removed)...
> Please help, I have designed a simple spreadsheet that
> uses Check Boxes and Option Buttons from the Control
> Toolbox and I am trying to make a small VBA routine to
> clear them all.
> I thought I could use:
>
> Sub Clear_cb_ob()
> Dim cb As CheckBox
> Dim ob As OptionButton
> For Each cb In Sheet1
> cb.Value = False
> Next
> For Each ob In Sheet1
> ob.Clear
> Next
> End Sub
>
> But I just get:
> Object doesn't support this property or method
>

Thanks Bob but your routine did not work, after I played
a bit I realised that it was my fault, I made a mistake
in my question.
The controls I used were created using the Forms Toolbar
not the Control Toolbox
Could you please help again.

Sub ClearControls()
Dim chk As CheckBox
Dim opt As OptionButton

For Each chk In ActiveSheet.Checkboxes
chk.Value = False
Next chk

For Each opt In ActiveSheet.OptionButtons
opt.Value = False
Next opt

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"newboy18" <(E-Mail Removed)> wrote in message
news:21f3e01c45b5c$754d3710$(E-Mail Removed)...
> Thanks Bob but your routine did not work, after I played
> a bit I realised that it was my fault, I made a mistake
> in my question.
> The controls I used were created using the Forms Toolbar
> not the Control Toolbox
> Could you please help again.
>

Thanks very much, I had been stuck on this for the past
couple of days

I have another question.
Everything was working but now I need to group the
optionboxes together into several groups.
I tried to ungroup everything, clear all the optionboxes
and then regroup everthing but it wont work.

Dim grp As ShapeRange

For Each grp In ActiveSheet.ShapeRange
grp.Select
Selection.ShapeRange.Ungroup.Select
Next grp

why would you want to group forms controls. Why would you want to group
anything using code?

grouping this way is not the way to make forms controls operate as a group.
You use a group box around them to do that.

--
Regards,
Tom Ogilvy

"newboy18" <(E-Mail Removed)> wrote in message
news:2161801c45b85$0e335680$(E-Mail Removed)...
> I have another question.
> Everything was working but now I need to group the
> optionboxes together into several groups.
> I tried to ungroup everything, clear all the optionboxes
> and then regroup everthing but it wont work.
>
> Dim grp As ShapeRange
>
> For Each grp In ActiveSheet.ShapeRange
> grp.Select
> Selection.ShapeRange.Ungroup.Select
> Next grp
>

That is what I did, create 5 option boxes and group them
using a group box, then create another 5 option boxes and
group them using another group box and so on.
Each 5 option boxes is used for a different topic.
My problem is that I wanted an option that would clear
them all, kind of a reset.

>-----Original Message-----
>why would you want to group forms controls. Why would
you want to group
>anything using code?
>
>grouping this way is not the way to make forms controls
operate as a group.
>You use a group box around them to do that.
>
>--
>Regards,
>Tom Ogilvy
>
>"newboy18" <(E-Mail Removed)> wrote
in message
>news:2161801c45b85$0e335680$(E-Mail Removed)...
>> I have another question.
>> Everything was working but now I need to group the
>> optionboxes together into several groups.
>> I tried to ungroup everything, clear all the
optionboxes
>> and then regroup everthing but it wont work.
>>
>> Dim grp As ShapeRange
>>
>> For Each grp In ActiveSheet.ShapeRange
>> grp.Select
>> Selection.ShapeRange.Ungroup.Select
>> Next grp
>>
>
>
>.
>

Sub Tester2()
For Each op In ActiveSheet.OptionButtons
op.Value = xlOff
Next

End Sub

works fine, pretty much the same as what Bob told you before.

--
Regards,
Tom Ogilvy



"newboy18" <(E-Mail Removed)> wrote in message
news:21daf01c45b9a$0e960360$(E-Mail Removed)...
> That is what I did, create 5 option boxes and group them
> using a group box, then create another 5 option boxes and
> group them using another group box and so on.
> Each 5 option boxes is used for a different topic.
> My problem is that I wanted an option that would clear
> them all, kind of a reset.
>
> >-----Original Message-----
> >why would you want to group forms controls. Why would
> you want to group
> >anything using code?
> >
> >grouping this way is not the way to make forms controls
> operate as a group.
> >You use a group box around them to do that.
> >
> >--
> >Regards,
> >Tom Ogilvy
> >
> >"newboy18" <(E-Mail Removed)> wrote
> in message
> >news:2161801c45b85$0e335680$(E-Mail Removed)...
> >> I have another question.
> >> Everything was working but now I need to group the
> >> optionboxes together into several groups.
> >> I tried to ungroup everything, clear all the
> optionboxes
> >> and then regroup everthing but it wont work.
> >>
> >> Dim grp As ShapeRange
> >>
> >> For Each grp In ActiveSheet.ShapeRange
> >> grp.Select
> >> Selection.ShapeRange.Ungroup.Select
> >> Next grp
> >>
> >
> >
> >.
> >

No, if I set a counter going I can see it is going round
the loop a few times, then it gives the error:
Run-time error 1004
Unable to set the Value property of the OptionButton
class.
I only had 5 buttons that were grouped by 1 group box.
If I ungroup the option buttons it works OK.
So I guess I need 3 loops
1 - will ungroup everthing
2 - will clear everything
3 - re-group everything.

>-----Original Message-----
>Sub Tester2()
>For Each op In ActiveSheet.OptionButtons
> op.Value = xlOff
>Next
>
>End Sub
>
>works fine, pretty much the same as what Bob told you
before.
>
>--
>Regards,
>Tom Ogilvy
>
>
>
>"newboy18" <(E-Mail Removed)> wrote
in message
>news:21daf01c45b9a$0e960360$(E-Mail Removed)...
>> That is what I did, create 5 option boxes and group
them
>> using a group box, then create another 5 option boxes
and
>> group them using another group box and so on.
>> Each 5 option boxes is used for a different topic.
>> My problem is that I wanted an option that would clear
>> them all, kind of a reset.
>>
>> >-----Original Message-----
>> >why would you want to group forms controls. Why would
>> you want to group
>> >anything using code?
>> >
>> >grouping this way is not the way to make forms
controls
>> operate as a group.
>> >You use a group box around them to do that.
>> >
>> >--
>> >Regards,
>> >Tom Ogilvy
>> >
>> >"newboy18" <(E-Mail Removed)> wrote
>> in message
>> >news:2161801c45b85$0e335680$(E-Mail Removed)...
>> >> I have another question.
>> >> Everything was working but now I need to group the
>> >> optionboxes together into several groups.
>> >> I tried to ungroup everything, clear all the
>> optionboxes
>> >> and then regroup everthing but it wont work.
>> >>
>> >> Dim grp As ShapeRange
>> >>
>> >> For Each grp In ActiveSheet.ShapeRange
>> >> grp.Select
>> >> Selection.ShapeRange.Ungroup.Select
>> >> Next grp
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>