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" 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

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" 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.

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