访问 ActiveX 复选框值，复选框名称为字符串

问题描述：

我有一个带有多个 ActiveX 复选框的 Excel 工作表.它们的名称是CheckBox1"、CheckBox2"、CheckBox3"等.如果我将它们称为变量，例如:

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

一切正常.

我想使用 For 循环将所有复选框的值加载到数组中，因此我必须将它们的名称引用为字符串，以创建连续的名称.我尝试了几种解决方案，例如:

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

显示运行时错误‘438’:对象不支持此属性或方法"

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

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

显示运行时错误 '1004':获取 CheckBoxes 类 Worksheet 的属性是 imossibble'

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

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

显示运行时错误‘438’:对象不支持此属性或方法"

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

在主题中，如何使用字符串作为名称引用 ActixeX 复选框值?可能吗?

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

答

我是这样尝试的，将对象定义为 OLEObject 而不是形状.s 有效，s2 失败.... 放在这里是为了允许格式化而不是在评论中.

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

访问 ActiveX 复选框值，复选框名称为字符串

相关推荐