making the check box uncheck when the worksheet activated

[asyamonique]

How can i make the all check boxes uncheck when the worksheet has been activated.
Cheers

 

[asyamonique]

Dim Arr As Variant
    Dim i As Integer
    Dim ws As Worksheet
    Dim obj As OLEObject
    Arr = Array("Sheet2", "Sheet3")
    For i = LBound(Arr) To UBound(Arr)
        Set ws = Worksheets(Arr(i))
        For Each obj In ws.OLEObjects
            If TypeName(obj.Object) = "CheckBox" Then
                obj.Object.Value = False
            End If
        Next obj
    Next i

 

[Rick Rothstein]

Are you sure you mean whenever the worksheet is activated? Even if the user accidentally clicks another tab and then click the original sheet's tab again? Assuming yes, and assuming you got your CheckBoxes from the Forms Toolbar, give this a try. Right click the name tab at the bottom of the worksheet containing your CheckBoxes, select "View Code" from the popup menu that appears, then copy/paste the following code into the code window that opened up...

Private Sub Worksheet_Activate()
  ActiveSheet.CheckBoxes.Value = 0
End Sub

 

[asyamonique]

Are you sure you mean whenever the worksheet is activated? Even if the user accidentally clicks another tab and then click the original sheet's tab again? Assuming yes, and assuming you got your CheckBoxes from the Forms Toolbar, give this a try. Right click the name tab at the bottom of the worksheet containing your CheckBoxes, select "View Code" from the popup menu that appears, then copy/paste the following code into the code window that opened up...

Private Sub Worksheet_Activate()
  ActiveSheet.CheckBoxes.Value = 0
End Sub

Thanks for ur reply Rick,
Sorry about missunderstanding ,
Its simply the test file with 20 questions and the answers are in options with (a-b-c-d) which located on worksheet1 with total of 80 check boxes.If the user finishes the answers they submit it by command button...what i want, the check boxes will be uncheck for the next user.
Hope its clear.
Many thanks

 

[asyamonique]

Dim oObj As OLEObject
    
    On Error Resume Next
    For Each oObj In ActiveSheet.OLEObjects
        If TypeName(oObj.Object) = "[B][COLOR=#ff0000]CheckBox[/COLOR][/B]" Then oObj.Object.Value = 0
    Next
    ActiveSheet.CheckBoxes = False

That was the answer of my question..
Thanks anyway.