Rob
Once again thanks for you reply. Most peculiar!!!
I have tried to open and run the macro in a number of different
machine/office combinations ie
W2k/Office 97
XP/ Office 2000
W98/Office 98
All give the same error.
I checked the properties for each checkbox. The first property listed
is (Name) (it's the only one in brackets??) and this is set to AAAA
etc. Have I got the wrong name property?
To create the checkboxes I used the control toolbox to create the
first, then copied it X times, and then renamed each checkbox. Would
the method of creating them make any difference?
Can you send me your spreadsheet, so I can try and run it on my PC.
Maybe it's a problem with my spreadsheet?
Would you be prepared to run my spreadhseet?
I tried to change the msgbox command to debug.print to see if this
made any difference but couldn't figure out where it output to. :-(
Any thoughts?
Many thanks again
Paul
"Rob Bovey" wrote in message ...
Hi Paul,
First let me appologize for that lame answer I gave you previously. It
was as wrong as it could be, but that's what happens when you post code
without testing it first. <g This time, I've created a worksheet with four
ActiveX checkbox controls that I've named AAA through DDD using the
Properties window. I then ran the following code:
Private Sub checkControls()
Dim ctlControl As OLEObject
Debug.Print ActiveSheet.Name
For Each ctlControl In ActiveSheet.OLEObjects
Debug.Print ctlControl.Name & " is " & ctlControl.Object.Value
Next ctlControl
End Sub
which displayed the following result in the Immediate window in all current
versions of Excel:
Sheet1
AAA is True
BBB is False
CCC is True
DDD is False
This is essentially identical to the code you originally posted, so I'm not
sure why you aren't getting the same results. The only thing I can suggest
at this point is to double check that you have actually renamed the controls
on your worksheet from their default values in the Properties window.
--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/