Checkbox click event triggered by VBA

[Atnas]

Hey Excel people

I have a problem.

I need to change the state of a checkbox, both from VBA code and from the sheet, but only have my code run when it's changed from the sheet.
My problem is that the click event is also triggered, when changing the state from VBA code.
I've tried using a MouseUp event, but I would like the user to be able to use a keyboard as well (and I can't change the state of the checkbox, because there is a Paste event in my code, that somehow stops the state from changing, weird, but not what I'm asking)

How would I go about solving my problem?

- Somehow detecting wether it was called from code?
- Making the mouseup event work?

Any help will be greatly appreciated.

 

[Jerry Sullivan]

Hi Atnas,

One option would be to use a Public Variable as a flag to that gets set to True right before the VBA code changes the Checkbox, and is reset to false by the Checkbox_Click.

Macro code example. (the Public Variable needs to be declared before any procedure code).

Public bCheckBoxCalledFromCode

Sub MyMacro()
    bCheckBoxCalledFromCode = True
    Sheets("Sheet1").CheckBox1 = True
End Sub

ActiveX Checkbox_Click code example

Private Sub CheckBox1_Click()
    If bCheckBoxCalledFromCode Then
        bCheckBoxCalledFromCode = False
        MsgBox "Checked by code"
    Else
        MsgBox "User Checked " & IIf(Me.CheckBox1.Value, "On", "Off")
    End If
End Sub

 

[Atnas]

Thanks Jerry.

I'm using this solution now

 

[bs0d]

What would be an option when then code under checkbox_click() is executed when resetting the checkbox value to true or false?

I also explored a mouse up event for my checkbox. However, I can't get it to "un-check" after the value has been set to true.

http://www.mrexcel.com/forum/excel-...x-values-onmouseup-clickevent-workaround.html

 

[Jerry Sullivan]

Hi bs0d,

After reading the other thread you referenced and your question here, your ultimate objective isn't clear to me.

It sounds like you want to be able to set the checkbox to either True or False through code and that for some reason just setting the Value property of the checkbox isn't working.

Typically, with no conflicting code in your worksheet, you should be able to simply set the Value property to True or False. I suspect you have code in your Checkbox_Click, Checkbox_Change event or elsewhere that is triggering and changing the state of the Checkbox.Value property. Have you tried placing a Breakpoint then stepping through the execution of the code to see what happens after a Checkbox.Value=False that ends up setting the Value back to True?

Do you need to use the MouseUp event or was that just one approach to workaround the problem?

Please clarify if I've misunderstood what you are wanting to do.

 

[mikerickson]

Application.Caller differentiates between being called from VBA or from a worksheet formula.

Sub Button1_Click()
    MsgBox WhereCalledFrom()
End Sub

Function WhereCalledFrom()
    Select Case TypeName(Application.Caller)
        Case "Error"
            WhereCalledFrom = "I was called from VBA"
        Case "String"
            WhereCalledFrom = "I was called by a control"
        Case "Range"
            WhereCalledFrom = "I was called by a worksheet formula"
    End Select
End Function

 

[Jerry Sullivan]

Good point Mike. That's much better than what I suggested in post #2.

 

[bs0d]

Application.Caller differentiates between being called from VBA or from a worksheet formula.

Sub Button1_Click()
    MsgBox WhereCalledFrom()
End Sub

Function WhereCalledFrom()
    Select Case TypeName(Application.Caller)
        Case "Error"
            WhereCalledFrom = "I was called from VBA"
        Case "String"
            WhereCalledFrom = "I was called by a control"
        Case "Range"
            WhereCalledFrom = "I was called by a worksheet formula"
    End Select
End Function

Interesting, when I execute this code, I provides "Error" when I would expect "String":

Private Sub myCheck_Click()

msgbox typeName(Application.Caller)

end sub

Thoughts?? This is an active x checkbox

 

[bs0d]

I can set the value, but I need to be able to manage the code execution. VBA is triggering the _click() code when I only need to set the appearance of the checkbox as clicked.

 

[mikerickson]

If myCheck_Click is tested by pressing F5, then Application.Caller is an error value
If myCheck_Click is tested by assigning it to a button (or a checkbox) and clicking that control, it should be a string.