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.

 

[bs0d]

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.

Yes, myCheck is the name of my checkbox. If you right-click the active x object and select view code, myCheck_click() is the sub routine. I added the code mentioned above from your example.

Then, when I click the checkbox using my mouse in the spreadsheet, the code returns "Error"... I tested this in a completely new spreadsheet with the same result.

New sheet --> Developer Tab --> Insert --> Check Box (Active X Control).
Draw box --> right-click --> view code --> add: MsgBox TypeName(Application.Caller)
Close code --> exit design mode --> click the box: Result = "Error".

This is using Excel 2013 on Windows 7

 

[bs0d]

For anyone else in a similar situation as myself, I received some feedback from Jon Peltier, who suggested the code below. His code code appears to work:

"You could declare a public variable in the Declarations section of a regular code module:"

Public gbDidNotClick As Boolean

Then in your calling code:

Sub ViewCheckbox()
    gbDidNotClick = True
    Sheets(“Sheet1″).OLEObjects(“myCheckBox”).Object.Value = 1
    gbDidNotClick = False
End Sub

And finally, in the control event code:

Private Sub myCheckBox_Click()
    If Not gbDidNotClick Then
        MsgBox “Checkbox code is running.”
    End If
End Sub