Clearing Check Box Upon File Close

  • Thread starter Thread starter Telecorder
  • Start date Start date
T

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?
 
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
 
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?
 
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
 
That did it! It was a Forms Toolbox Check Box so renaming the ws & CheckBox
number for your first code addressed the issue...
Thanks!
 
Back
Top