Check Boxes and Option Buttons

[newboy18]

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

 

[Bob Phillips]

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]

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.

 

[Bob Phillips]

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)

 

[newyboy18]

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

 

[newboy18]

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

 

[Tom Ogilvy]

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.

 

[newboy18]

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.

 

[Tom Ogilvy]

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.

 

[newboy18]

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.

 

[Tom Ogilvy]

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.

 

[newboy18]

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.

 

[Tom Ogilvy]

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.

 

[newboy18]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[newboy18]

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

 

[Tom Ogilvy]

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

 

[newboy18]

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.