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?

Nathan_Sav · Accepted Answer · 2016-01-20 09:42:52Z

3

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

answered Jan 20, 2016 at 9:42

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Limak Over a year ago

Thanks, the ActiveSheet.OLEObjects("CheckBox1").Object.Value works perfect.

Collectives™ on Stack Overflow

Access to ActiveX checkbox value with name of checkbox as String

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related