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