excel spreadsheet referencing an activex control

1
votes

I am using Excel 2013. I have added an activex control to my spreadsheet. The control is a checkbox which I have named chkAD1. My spreadsheet is called "timeseries_AD".

I am trying to reference the checkbox to check its value however without any joy. I have tried the lines below,

 worksheets("timeseries_AD").OleObjects("chkAD1").Value

This results in the error message "unable to get the OLEObjects property of the worksheet class".

I have read that an activex control has two names. One is the name of the shape that contains the control the other is the code name. I'm not sure which one I have changed. I clicked on my control and in the Name Box renamed it to "chkAD1". Is that the shape name or code name I have changed?

UPDATE - Apologies

Sorry the control I added is not an activex control it is actually a form control.

1
vbaexcel
go to the sheet with the checkbox. get in design mode (button next to insert button) and then in design mode double click the checkbox. – Goos van den Bekerom
What does Msgbox worksheets("timeseries_AD").Shapes("chkAD1").Name return? An error? – Rory
@Goosebumbs I'm in design mode and double clicking on the control doesn't do anything? – mHelpMe
@Rory that line returns me the name i.e. "chkAD1" – mHelpMe
make sure you select the checkbox before you double click it – Goos van den Bekerom

1 Answers

2
votes

I tried this and it worked for me.
When I check the box I get a messagebox that says TRUE.
And when I uncheck it I get a messagebox that says FALSE

Private Sub CheckBox1_Click()
    MsgBox CheckBox1.Value
End Sub