在VBA事件处理程序中获取对Forms复选框的引用

I have some Forms Checkboxes in Excel 2010. I need to perform some common code when they are clicked. To do this, I'd like to pass a reference to the Checkbox, but so far I'm only able to get it typed as a shape.

我在Excel 2010中有一些表单复选框。我需要在单击时执行一些常用代码。为此,我想传递一个对Checkbox的引用,但到目前为止我只能将它作为一个形状输入。

To preempt the question, yes, they need to be Form Checkboxes and not ActiveX Checkboxes.

要抢占问题,是的,它们需要是表格复选框而不是ActiveX复选框。

I'm a novice with VBA, so any help is appreciated.

我是VBA的新手,所以任何帮助都表示赞赏。

Sub CheckBox1_Click()
    'I really want this reference to be a Checkbox, not a Shape
    Dim shape As Shape
    Set shape = ActiveSheet.Shapes("Check Box 1")            

    DoSomething(shape)
End Sub

Sub DoSomething(MSForms.CheckBox)
    'I need the reference to be a checkbox as I need to check 
    'whether it's checked or not here
End Sub

2 个解决方案

#1

This is similar to Siddharth's but adds the ControlFormat property of the Shape. ControlFormat gets you the Intellisense for the CheckBox, in this case Value:

这与Siddharth类似,但添加了Shape的ControlFormat属性。 ControlFormat为您提供CheckBox的Intellisense,在本例中为Value:

Sub CheckBox1_Click()
Dim chk As Shape

Set chk = ActiveSheet.Shapes(Application.Caller)
With chk.ControlFormat
    If .Value = True Then
        MsgBox "true"
    Else
        MsgBox "false"
    End If
End With
End Sub

#2

In such a scenario, don't have different click event for all checkboxes. Have just one. And use Application.Caller to get the name of the ckeckbox which called it. Pass that as a String to the relevant sub and then work with it.

在这种情况下,所有复选框都没有不同的单击事件。只有一个。并使用Application.Caller获取调用它的ckeckbox的名称。将其作为String传递给相关的子,然后使用它。

UNTESTED

Sub CheckBoxMain_Click()
    Dim sName As String

    sName = Application.Caller

    DoSomething (sName)
End Sub

Sub DoSomething(sCheck As String)
    Dim shp As shape

    Set shp = ActiveSheet.Shapes(sCheck)

    With shp
        '~~> Do something
    End With
End Sub

You could also combine the two into one as well and link it with all checkboxes.

您也可以将两者合并为一个,并将其与所有复选框相关联。

Sub DoSomething()
    Dim shp As shape

    Set shp = ActiveSheet.Shapes(Application.Caller)

    With shp
        '~~> Do something
    End With
End Sub

#1