I have code that refers to a checkbox control on a worksheet like this:

X = chkSRAP.Value

However, since that worksheet could potentially be deleted and cause problems in the future, I'd like to refer to the checkbox along these lines:

X = Sheets("SHRP Input").OLEObjects("chkSRAP").Value

Except this doesn't work. I get:
Run-time error '438':
Object doesn't support this property or method

Now, this sort of syntax seems to work for textboxes, comboboxes, toggle buttons and spinner controls. But not checkboxes.

EDIT: I tried recording a macro of me selecting the checkbox, and got this:

ActiveSheet.Shapes.Range(Array("chkSRAP")).Select

However, when I try:

X = Sheets("SHRP Input").Shapes.Range(Array("chkSRAP")).Value

I get the same error.


Any idea how I should alter my syntax so I can refer to the checkbox?

Dennis

Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("SHRP Input")
WS.OLEObjects("chkSRAP").Object.Value = 1

Apparently microsoft thought it would be too good to be true if we could simply refer to the Checkbox's value. No, the object of the checkbox's value. Pretty ......mmm I'm not finding a word I can say on this forum. Nevermind.

Isaac,

So, basically insert ".Object" and it works.

Thanks. I'm sure there's an explanation that makes this seem less arbitrary, especially when compared to the syntax for all the other controls.

Dennis

Dennis

That syntax doesn't work for ActiveX controls.

Worked for me, Norie

How can you tell if a control is ActiveX or not?

I didn't create this workbook originally, so this isn't just a case of senility.

Dennis

Dennis

A non-scientific way to tell is to right click the control.

If Properties is on the dropdown it's ActiveX.

Isaac

I was referring to the original code.

The code you posted used Object as well, which is the solution.

Ugh.

This whole post was un-necessary. The non-checkbox subs all had "On Error Resume Next" in them. Norie's right - I should've been using the ".Object" bit for ALL of the controls.

I would've spotted them if I had coded them, but the original author decided to put them above the "Dim" statements, which I personally never do.

Dennis

Dennis

Isaac posted the correct code, not me.

LOL, okay this is apparently getting more confusing, not less so...

I posted code that I thought worked for some controls, but not checkboxes.

It turns out that the code I posted works for NONE of them. Instead, I had misled myself into thinking this because SOME of the subroutines I've been working on had included "On Error Resume Next" above the Dim statements, where I'd failed to notice.

So, bottom line: Isaac's suggestion was right. My assumption that my posted code worked on ANYTHING was incorrect.

My apologies for the confusion... My frustration levels with this particular trouble-ridden workbook has me at my wit's end.

Dennis