View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Rob Bovey Rob Bovey is offline
external usenet poster
 
Posts: 811
Default Accessing ActiveX Controls using VBA - can't use Control.Name to access.

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