Option Explicit

I'm trying to enusure that all checkboxes are returned to unchecked
when the workbook opens. All the boxes are on one sheet and were from
the Forms menu. I was very kindly supplied with this code but it
doesn't seem to be working on my workbook. I assume I'm doing something
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 Sub


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=512611

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"
wrote in message
...

Option Explicit

I'm trying to enusure that all checkboxes are returned to unchecked
when the workbook opens. All the boxes are on one sheet and were from
the Forms menu. I was very kindly supplied with this code but it
doesn't seem to be working on my workbook. I assume I'm doing something
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 Sub


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile:
http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=512611

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 wrote:

Option Explicit

I'm trying to enusure that all checkboxes are returned to unchecked
when the workbook opens. All the boxes are on one sheet and were from
the Forms menu. I was very kindly supplied with this code but it
doesn't seem to be working on my workbook. I assume I'm doing something
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 Sub

--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=512611

--

Dave Peterson

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


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=512611