Uncheck check boxes

[Brisbane Rob]

Option Explicit

I'm trying to enusure that all checkboxes are returned to unchecke
when the workbook opens. All the boxes are on one sheet and were fro
the Forms menu. I was very kindly supplied with this code but i
doesn't seem to be working on my workbook. I assume I'm doing somethin
wrong but it's in the code sheet OK.

Can anyone give me a pointer as to where I'm going wrong?

Thanks


Sub auto_open()
Dim wks As Worksheet
Dim OLEObj As OLEObject

Set wks = Worksheets("sheet1")

If wks.CheckBoxes.Count > 0 Then
wks.CheckBoxes.Value = xlOff
End If

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Su

 

[Bob Phillips]

Sub auto_open()
Dim wks As Worksheet
Dim cb As CheckBox

Set wks = Worksheets("Sheet1")

For Each cb In wks.CheckBoxes
Value = xlOff
Next cb

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brisbane Rob" <[email protected]>
wrote in message
news:[email protected]...

 

[Dave Peterson]

If all your checkboxes were from the Forms toolbar, you don't need that second
half:

Sub auto_open()
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

If wks.CheckBoxes.Count > 0 Then
wks.CheckBoxes.Value = xlOff
End If

End Sub

Unless you have an ungodly amount of checkboxes, this should get them unchecked
all at once.

If you do have that ungodly amount, then Bob's code will cycle through each one.

 

[Brisbane Rob]

Once again Bob, I'm indebted to you. Thanks