Clearing Check Box Upon File Close

[Telecorder]

How would one code to confirm status of a Check Box on close and, if it's
checked,
reset it to 'unchecked' so it will be unchecked upon next open?

 

[FSt1]

hi
you could use code similar to this...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("sheet1").CheckBox1.Value = False
End Sub
this code wil just set the checkbox to false if it's is checked and if it's
not then no harm.

regards
FSt1

 

[Telecorder]

Thanks for the suggestion - But...
receive the error--
Run Time Error '438'
Object does not support this property or method.

I found a possible If CheckBox(i) = True Then possibility but haven't
determined how to write the correct syntax or reference to code it

Any other ideas?

 

[Mike H]

Hi,

It depends on the type. From the 'Forms' toolbox

Sheets("Sheet1").CheckBoxes("Check Box 1").Value = xlOff

or to un-check them all
Sheets("Sheet1").CheckBoxes.Value = False

If they are from the Control toolbox

Sub Control_Toolbox()
'Control toolbox
Dim ws As Worksheet
Dim obj As OLEObject
Set ws = Sheets("Sheet1")
For Each obj In ws.OLEObjects
If TypeName(obj.Object) = "CheckBox" Then
obj.Object.Value = False
End If
Next obj
End Sub


You could put these in the before colse workbook event.

Mike

 

[Telecorder]

That did it! It was a Forms Toolbox Check Box so renaming the ws & CheckBox
number for your first code addressed the issue...
Thanks!