Check Boxes and Option Buttons
 

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

Check Boxes and Option Buttons
 

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" wrote in message
...
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

Check Boxes and Option Buttons
 

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.

Check Boxes and Option Buttons
 

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" wrote in message
...
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.

Check Boxes and Option Buttons
 

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

Check Boxes and Option Buttons
 

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

Check Boxes and Option Buttons
 

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" wrote in message
...
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

Check Boxes and Option Buttons
 

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" wrote
in message
...
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



.

Check Boxes and Option Buttons
 

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" wrote in message
...
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" wrote
in message
...
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



.

Check Boxes and Option Buttons
 

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" wrote
in message
...
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" wrote
in message
...
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



.



.

Check Boxes and Option Buttons
 

Again, there is no reason to group anything. stop grouping things. It isn't
required and does nothing for your functionality.

Ungroup things and leave them alone.



--
Regards,
Tom Ogilvy

"newboy18" wrote in message
...
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" wrote
in message
...
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" wrote
in message
...
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



.



.

Check Boxes and Option Buttons
 

How can I group option boxes together if I don't group
them?
What I have is a questionare that asks users to select 1
of 5 different answers from several different questions.
So I want the each 5 option boxes to be grouped together.


-----Original Message-----
Again, there is no reason to group anything. stop
grouping things. It isn't
required and does nothing for your functionality.

Ungroup things and leave them alone.



--
Regards,
Tom Ogilvy

"newboy18" wrote
in message
...
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" wrote
in message
...
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"
wrote
in message
...
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



.



.



.

Check Boxes and Option Buttons
 

You group option buttons by placing a group box around them. that is it.
You don't use code, you don't select them, do right click and group. You
don't use the group object.

Physically having the group box around them groups them for the purpose you
require. If you only do that, then the code works fine.

--
Regards,
Tom Ogilvy

"newboy18" wrote in message
...
How can I group option boxes together if I don't group
them?
What I have is a questionare that asks users to select 1
of 5 different answers from several different questions.
So I want the each 5 option boxes to be grouped together.


-----Original Message-----
Again, there is no reason to group anything. stop
grouping things. It isn't
required and does nothing for your functionality.

Ungroup things and leave them alone.



--
Regards,
Tom Ogilvy

"newboy18" wrote
in message
...
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" wrote
in message
...
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"
wrote
in message
...
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



.



.



.

Check Boxes and Option Buttons
 

I am confused, I agree with what you are saying.
I created 5 option buttons and then grouped them with a
Group Box and then I created 5 more option buttons and so
on.
What I want is a routine that will clear all of the
option buttons, a reset function, but it won't work
because the option buttons are grouped, if I ungroup them
manualy then the routine works.


-----Original Message-----
You group option buttons by placing a group box around
them. that is it.
You don't use code, you don't select them, do right
click and group. You
don't use the group object.

Physically having the group box around them groups them
for the purpose you
require. If you only do that, then the code works fine.

--
Regards,
Tom Ogilvy

"newboy18" wrote
in message
...
How can I group option boxes together if I don't group
them?
What I have is a questionare that asks users to select
1
of 5 different answers from several different
questions.
So I want the each 5 option boxes to be grouped
together.


-----Original Message-----
Again, there is no reason to group anything. stop
grouping things. It isn't
required and does nothing for your functionality.

Ungroup things and leave them alone.



--
Regards,
Tom Ogilvy

"newboy18" wrote
in message
...
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"
wrote
in message
...
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"
wrote
in message
...
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



.



.



.



.

Check Boxes and Option Buttons
 

I did the same. An a New worksheet

Put 5 option buttons on the worksheet.

Put a group box around them

Linked the first to a cell (all are now linked)

Put 5 option buttons on a worksheet

Put a group box around them

Linked the first to a cell (all are now linked)

select an option button in each

ran the code I provided.

It worked with no problem. All option buttons were cleared (and linked
cells showed zero).

all controls from the forms toolbar.

--
Regards,
Tom Ogilvy

"newboy18" wrote in message
...
I am confused, I agree with what you are saying.
I created 5 option buttons and then grouped them with a
Group Box and then I created 5 more option buttons and so
on.
What I want is a routine that will clear all of the
option buttons, a reset function, but it won't work
because the option buttons are grouped, if I ungroup them
manualy then the routine works.


-----Original Message-----
You group option buttons by placing a group box around
them. that is it.
You don't use code, you don't select them, do right
click and group. You
don't use the group object.

Physically having the group box around them groups them
for the purpose you
require. If you only do that, then the code works fine.

--
Regards,
Tom Ogilvy

"newboy18" wrote
in message
...
How can I group option boxes together if I don't group
them?
What I have is a questionare that asks users to select
1
of 5 different answers from several different
questions.
So I want the each 5 option boxes to be grouped
together.


-----Original Message-----
Again, there is no reason to group anything. stop
grouping things. It isn't
required and does nothing for your functionality.

Ungroup things and leave them alone.



--
Regards,
Tom Ogilvy

"newboy18" wrote
in message
...
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"
wrote
in message
...
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"
wrote
in message
...
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



.



.



.



.

Check Boxes and Option Buttons
 

Are you sure you know what a groupbox is? It is another control on the
forms toolbar.

--
Regards,
Tom Ogilvy

"newboy18" wrote in message
...
I am confused, I agree with what you are saying.
I created 5 option buttons and then grouped them with a
Group Box and then I created 5 more option buttons and so
on.
What I want is a routine that will clear all of the
option buttons, a reset function, but it won't work
because the option buttons are grouped, if I ungroup them
manualy then the routine works.


-----Original Message-----
You group option buttons by placing a group box around
them. that is it.
You don't use code, you don't select them, do right
click and group. You
don't use the group object.

Physically having the group box around them groups them
for the purpose you
require. If you only do that, then the code works fine.

--
Regards,
Tom Ogilvy

"newboy18" wrote
in message
...
How can I group option boxes together if I don't group
them?
What I have is a questionare that asks users to select
1
of 5 different answers from several different
questions.
So I want the each 5 option boxes to be grouped
together.


-----Original Message-----
Again, there is no reason to group anything. stop
grouping things. It isn't
required and does nothing for your functionality.

Ungroup things and leave them alone.



--
Regards,
Tom Ogilvy

"newboy18" wrote
in message
...
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"
wrote
in message
...
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"
wrote
in message
...
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



.



.



.



.

Check Boxes and Option Buttons
 

Yes, I think I have the same as you.
If I right click on one of the optionbuttons in the first
group then the whole group is selected and I can select
Format Object, (not what I want)

If I Ungroup them first, then right click on a
optionbutton, I can select just 1 of the optionbuttons,
this time the short menu allows me to Format Control.
From here I can select the Control tab and then Unselect.

It is the groups that are stopping me from running the
code.
If I can unselect the groups in the VBA before clearing
the optionbuttons then I am sure it will work

-----Original Message-----
Are you sure you know what a groupbox is? It is another
control on the
forms toolbar.

Check Boxes and Option Buttons
 

No, that is not how you group option buttons. You don't know what a
groupbox is. Your method is grouping shapes to form a composite shape. It
is not the provided method for grouping optionbuttons - the groupbox control
is provided for this purpose. A groupbox is the equivalent of a Frame on a
userform. It is an actual square/rectangular shape that you put around the
option buttons. this is how you properly group option buttons. It also
provides a visible feedback to the user. You put you five optionbutton on
the worksheet, then click the groupbox control in the forms toolbar, and
rubberband the control around the 5 optionbuttons. the optionbuttons are
not selected, they simply are contained within the border of the groupbox.

Open a new workbook and put this macro in a general module. Then run it:

Sub Macro2()
Dim op1 As OptionButton, op2 As OptionButton
Dim op3 As OptionButton, op4 As OptionButton
Dim op5 As OptionButton
Dim grpbx As GroupBox
Set op1 = ActiveSheet. _
OptionButtons.Add(337, 27.75, 51.75, 17.25)
Set op2 = ActiveSheet. _
OptionButtons.Add(337, 57.75, 57, 17.25)
Set op3 = ActiveSheet. _
OptionButtons.Add(337.25, 90, 60.75, 17.25)
Set op4 = ActiveSheet. _
OptionButtons.Add(337.75, 121.5, 65.25, 17.25)
Set op5 = ActiveSheet. _
OptionButtons.Add(337, 156, 58.5, 27.75)
Set grpbx = ActiveSheet. _
GroupBoxes.Add(297.75, 21, 182.25, 194.25)
op1.LinkedCell = "$A$1"
grpbx.Caption = "ABCD"
Application.ScreenUpdating = False
Application.ScreenUpdating = True
End Sub


--
Regards,
Tom Ogilvy


"newboy18" wrote in message
...
Yes, I think I have the same as you.
If I right click on one of the optionbuttons in the first
group then the whole group is selected and I can select
Format Object, (not what I want)

If I Ungroup them first, then right click on a
optionbutton, I can select just 1 of the optionbuttons,
this time the short menu allows me to Format Control.
From here I can select the Control tab and then Unselect.

It is the groups that are stopping me from running the
code.
If I can unselect the groups in the VBA before clearing
the optionbuttons then I am sure it will work

-----Original Message-----
Are you sure you know what a groupbox is? It is another
control on the
forms toolbar.

Check Boxes and Option Buttons
 

Thanks for all your patience.
I created a counter and found that it failed when it had
gone round the loop 27 times so I changed the loop to set
the checkboxes, that way I could see which one was
causing the problem.
It turned out that I had Ctrl Right clicked on some
checkboxes in one of the groups and grouped them, it was
only these few that were causing the problem anyway I
have fixed it now.
Thanks for all your help.