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 — user2140173
– user2140173, Commented Dec 16, 2013 at 15:11

Siddharth Rout · Accepted Answer · 2013-12-16 15:04:52Z

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, 2013 at 15:04

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

4 Comments

MgSam Over a year ago

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.

Siddharth Rout Over a year ago

@MgSam: That is because you said it's a Form Control and not an ActiveX control? For form controls you can use one sub

MgSam Over a year ago

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?

Siddharth Rout Over a year ago

stackoverflow.com/questions/11991308/…

Doug Glancy · Accepted Answer · 2013-12-16 15:11:53Z

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, 2013 at 15:11

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

3 Comments

Siddharth Rout Over a year ago

stackoverflow.com/questions/11991308/…

Doug Glancy Over a year ago

@SiddharthRout, I looked at the link. What does your comment mean?

Siddharth Rout Over a year ago

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

Collectives™ on Stack Overflow

Get reference to Forms checkbox in VBA event handler

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related