Access to ActiveX checkbox value with name of checkbox as String

Question

I have an Excel worksheet with multiple ActiveX checkboxes. Their names are "CheckBox1", "CheckBox2", "CheckBox3" etc. If I refer to them as variables, for example:

If CheckBox1 = True Then
MsgBox "OK"
EndIf

everything works fine.

I want to load to array values of all my checkboxes with a For loop, so I have to reference to their names as String, to create consecutive names. I have tried several solutions, such as:

Dim CheckBox As Shape
Set CheckBox = ActiveSheet.Shapes("CheckBox1")
If CheckBox.OLEFormat.Object.Value = 1 Then
MsgBox "OK"
End If

Showing "Run-time error '438': Object doesn't support this property or method"

If ActiveSheet.CheckBoxes("CheckBox1").Checked = False Then
MsgBox "OK"
End If

Showing "Run-time error '1004': Taking properties of CheckBoxes class Worksheet is imossibble'

If ActiveSheet.Shapes("CheckBox1").Value = xlOn Then
MsgBox "OK"
End If

Showing "Run-time error '438': Object doesn't support this property or method"

As in subject, how can I refer to ActixeX checkbox value with String as name? Is it possible?

Answer 1

I tried like so, defining the object as an OLEObject rather than a shape. s works, s2 fails.... Put in here to allow formatting rather than in comment.

Sub test()

Dim s As OLEObject
Dim s2 As Shape

Set s = ActiveSheet.OLEObjects("CheckBox1")
Set s2 = ActiveSheet.Shapes("CheckBox1")

Debug.Print s.Object.Value
Debug.Print s2.OLEFormat.Object.Value

End Sub