Get reference to Forms checkbox in VBA event handler

Question

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

Worksheet's checkboxes from Form category are of Shape type. You can't *cast* them to a CheckBox type — , Dec 16 '13 at 15:11

score 4 · Answer 1 · answered Dec 16 '13 at 15:04

4

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.

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

answered Dec 16 '13 at 15:04

Siddharth Rout

147,039
17
206
250

My understanding was that Excel did not allow you to have a common event handler for more than one control. Is that not true? Believe me, I'd like nothing more. Still, this doesn't address my problem. I need the shape typed as a Checkbox with a Value property, in your code it's still typed as a Shape. – MgSam Dec 16 '13 at 15:07
@MgSam: That is because you said it's a Form Control and not an ActiveX control? For form controls you can use one sub – Siddharth Rout Dec 16 '13 at 15:08
Ahh, thanks. That's very helpful. Do you know how I could check it's Value property to see if it's checked or not? – MgSam Dec 16 '13 at 15:10
http://stackoverflow.com/questions/11991308/how-do-i-use-checkboxes-in-an-if-then-statement-in-excel-vba-2010 – Siddharth Rout Dec 16 '13 at 15:15

score 2 · Accepted Answer · answered Dec 16 '13 at 15:11

2

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

answered Dec 16 '13 at 15:11

Doug Glancy

27,214
6
67
115

http://stackoverflow.com/questions/11991308/how-do-i-use-checkboxes-in-an-if-then-statement-in-excel-vba-2010 – Siddharth Rout Dec 16 '13 at 15:15
@SiddharthRout, I looked at the link. What does your comment mean? – Doug Glancy Dec 16 '13 at 16:20
I already upvoted your answer :) I posted the above comment after I posted it in the comments of my answer. Just wanted to let you know that it was already covered :) – Siddharth Rout Dec 16 '13 at 16:21

Get reference to Forms checkbox in VBA event handler

2 Answers2

Linked