Clear all Checkboxes on a spreadsheet to original form 1

[justagrunt]

Hi,
if i have some checkboxes on a spreadsheet (created by the controlbox wizard,15 of them) and the user selects them and inadvertently saves and then exits, how can I reset those checkboxes so that when the next user opens the sheet that all checkboxes are returned to zero.
Example
Check1 is true causes check2 and check 3 to be hidden.
If check 2 selected check1 and check 3 hidden.
Check 3 causes check1 and check 2 to be hidden.
If any of them are unchecked all checkboxes become visible.
if the sheet is saved with any of these set, when the sheet is reopened, only that checkbox is visible and set.
What i would like to do is ensure that the sheet is fresh on open.
Is this possible,
I have tried the following unsuccessfully.
Sub ClearAllCB()

Dim oleCheck As OLEObject

For Each oleCheck In Sheet1.OLEObjects
If TypeName(oleCheck.Object) = "CheckBox" Then
oleCheck.Object.Visible = True
oleCheck.Object.Value = False

End If
Next oleCheck

End Sub

I have even tried worksheets(1).check1.visible=true
and so on to make all visible and

worksheets(1).check1=false etc to reset
all to no avail.
any Ideas?
Kind Regards
bill

 

[cLFlaVA]

justagrunt-

Here's a Macro that you can call when the Workbook opens:

Sub SetAllToFalse()
    Dim ctrl As OLEObject
    Dim ct As Object
    
    Application.ScreenUpdating = False
    For Each ctrl In Sheet1.OLEObjects
        If TypeName(ctrl.Object) = "CheckBox" Then
            ctrl.Object.Value = False
        End If
    Next ctrl
    Application.ScreenUpdating = True
End Sub

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[cLFlaVA]

ps. you can remove the Dim ct as Object line - I was using it for testing purposes.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[cLFlaVA]

Sorry, I'm a damn mess.

here's the code that will work:

Sub SetAllToFalse()
    Dim ctrl As OLEObject
    
    Application.ScreenUpdating = False
    For Each ctrl In Sheet1.OLEObjects
        If TypeName(ctrl.Object) = "CheckBox" Then
            ctrl.Object.Value = False
            ctrl.Visible = True
        End If
    Next ctrl
    Application.ScreenUpdating = True
End Sub

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[justagrunt]

Cheers cLFlaVA
Will expedite,
Thanks
Bill

 

[justagrunt]

Hi cLFlaVA
Got it working Cheers.
But to get it to work I had to create another sub Autpen which called up
SetAllToFalse()

So the whole thing became

Sub Auto_open()
SetAllToFalse
End Sub

Sub SetAllToFalse()
Dim ctrl As OLEObject

Application.ScreenUpdating = False
For Each ctrl In Sheet1.OLEObjects
If TypeName(ctrl.Object) = "CheckBox" Then
ctrl.Object.Value = False
ctrl.Visible = True
End If
Next ctrl
Application.ScreenUpdating = True
End Sub

Don't ask me why it was a spurr of the moment when it didn't run on open but did when I went to the immediate window or ran the macro from the Tool Bar.

Many Thanks,
Kind Regards
Bill