Search code examples
vbaexceloffice-2010

Get reference to Forms checkbox in VBA event handler


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.

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

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

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

Solution

  • 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:

    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