Hi Paul,

Try it like this:

MsgBox Control.Object.CodeName & " is " & Control.Object.Value

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Paul Rogers" wrote in message
m...
Dear All

I am trying to create a list of (active x) checkboxes in excel 97. as
follows

cell ref: A1 B1 C1
AAAA Description 1 <activex checkbox renamed to AAAA
A2 B2 C2
AAAB Description 2 <activex checkbox renamed to AAAB
.....etc

I then need a vba macro to work it's way down column A and for each
row check whether the checkbox named the same as the value in the cell
is true or false. Using posts on this site I have managed to figure
out how to access the name property of each checkbox.

My vba script won't find any checkboxes with the correct name. ON
checking this out I have discovered that while I have changed the name
for each checkbox in the control box/properties the vba code stil
finds the original name of the checkbox, ie if I run a macro such as

Private Sub checkControls()
MsgBox ActiveSheet.Name
For Each Control In ActiveSheet.OLEObjects
MsgBox Control.Name & " is " & Control.Object.Value
Next Control
End Sub

the name that appears is still Checkbox1

ie if I run the macro the first reponse I would expect is

AAAA is TRUE

when in actual fact what I get is

Checkbox1 is TRUE.

Can any one shed light on why this is happening and (more importantly)
how to fix it?

Having read various posts I now feel I should have probably done this
some other way, but I'm committed to this approach having sent the
spreadsheet out to quite a few people.

This is driving me nuts. Please hel;p

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/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Paul Rogers" wrote in message
m...
Rob

Many thanks for your reply.

Using either

MsgBox Control.Object.CodeName & " is " & Control.Object.Value

or

MsgBox Control.CodeName & " is " & Control.Object.Value

results in

Runtime Error 438:
Object doesn't support this property or method

Can you suggest anything else that might help?

Many thanks

Paul
"Rob Bovey" wrote in message
...
Hi Paul,

Try it like this:

MsgBox Control.Object.CodeName & " is " & Control.Object.Value

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

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/