Checking a Check Box using VBA

[NathanW]

Hey everybody,

I'm just wondering if it's possible to check or uncheck a check box in VBA? If it is, I would really like to know how please =)

 

[mwrohr]

Using a Form checkbox control

    ActiveSheet.Shapes.Range(Array("Check Box 1")).Value = xlOn

    ActiveSheet.Shapes.Range(Array("Check Box 1")).Value = xlOff

 

[NathanW]

Thank you very much for the response, but that doesn't seem to work for me. I get the error "Run-time error '438': Object doesn't support this property or method".

 

[mwrohr]

May be because I'm in a different version of Excel so my macro recorder has different syntax. Go ahead and create a Checkbox form control, then record the macro, right click the checkbox (in design mode) and go to Format Control. On the Control tab set the value to on and then do it for off. Should give you the proper syntax for your environment.

 

[mwrohr]

actually, my bad - I guess you have to select the checkbox before you can do anything with it.

    ActiveSheet.Shapes.Range(Array("Check Box 1")).Select
    With Selection
        .Value = xlOn
        .LinkedCell = ""
        .Display3DShading = False
    End With

 

[NathanW]

Ah ha! That worked! Thank you very much!

 

[PatHay]

I had the same issue and this solution has helped me greatly, thank you.