Macro Help

[Guest]

I reckon this may be simple, but I'm stuck;

I have the following code

Private Sub clearwateraudit_Click()
With Sheet1
..CheckBoxes.Value = False
End With
End Sub

This is used to uncheck all check boxes on sheet 1

I want to amend this to uncheck buttons on sheet 2 and sheet 3 also!


Thanks for your help

 

[Susan]

this is what you need - you have to go through each checkbox
individually...........

Dim oControl As Control
For Each oControl In Me.Controls
If TypeOf oControl Is msforms.CheckBox Then
oControl.Value = false
End If
Next oControl

then do the same thing
with sheet2
end with

with sheet3
end with

susan

 

[Susan]

actually, change
..me controls
to just
..controls

..me controls implies a userform, which you are not using.
susan

 

[Guest]

Hi!

Try this one, this should work ok.

Private Sub clearwateraudit_Click()
Dim oSheet As Worksheet
Dim oControl As OLEObject
Dim i As Integer
i = 1
For Each oSheet In Worksheets
With Sheets(i)
For Each oControl In .OLEObjects
If TypeName(oControl.Object) = "CheckBox" Then
' if you use Control Toolbox objects
oControl.Object.Value = False
' if you use Forms toolbar on a worksheet
.CheckBoxes.Value = xlOff
End If
Next oControl
End With
i = i + 1
Next oSheet
End Sub

Regards,
Kari J Keinonen