Bug in ActiveX 'Check Box" control? - problem seen in Excel

[groston]

On my Excel spreadsheet, I have placed a ActiveX Check Box (using the Control Toolbar). I have attached to this control a VBA routine that fires on the Click event, that is, I created code for myCheckBox_Click.

When i Click the box, the code is executed as expected. However, there are two strange behaviors:
1 - The value stored in the checkbox is changed prior to my code being executed. Specifically, If I am clicking to uncheck the box, you would think to see that its value is true, however, it is false.
2 - (And I think this is a bug) When I programmatically change the value of the check box, the Click event code is executed. This makes no sense at all and makes code execute that I do not want to run.

Obviously, I need some help getting this squared away. Can you offer any suggestions or point me to good references to clear up this issue.

Thanks.

 

[VulcanJedi]

On your 1: if you are UNclicking, that would make the box false. By unclicking you ARE triggering the event handler, thus your code would run...this sounds like its running as written.
2. I replicated this, this is strange, no suggestions on this one. however your code is working corrected in reference to line 1/. Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[groston]

I have uploaded a very small (10 kb) spreadsheet to my web-site that exhibits this strange behavior. I welcome you to review this spreadsheet (a sum total of about 12 lines of code) and tell me how to eliminate the strange behavior (#2 above).

Thanks.

http://www.pairofdocs.net/testbug.zip

 

[VBslammer]

The Click() Event as well as the Change() Event is generated when you change the checkbox value. To remedy, put your code in the MouseUp() Event. Also, since your code is local to the sheet, you can omit the explicit references to the checkbox:

Private Sub CheckBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  If Not CheckBox1 Then
        Range("B2") = 5
    Else
        Range("B2") = 1
    End If
End Sub

Private Sub CommandButton1_Click()
  CheckBox1 = Not CheckBox1
End Sub

VBSlammer

Unemployed in Houston, Texas