I have uploaded a very small (10 kb) spreadsheet to my web-site (http://www.pairofdocs.net/testbug.zip) that exhibits some strange behavior. On this spreadsheet, there are two Active X controls (placed there using the Control Toolbar). The first is a Command Button, the second a Check Box. I have attached Click event handler code to both of these controls.
When the Check Box is checked, the value 5 appears in cell B2. When it is clear (clicked again), the value 1 appears in cell B2. The sophisticated code to make this work is shown here:
Private Sub CheckBox1_Click()
If (Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = False) Then
Range("B2") = 5
Else
Range("B2") = 1
End If
End Sub
The code attached to the Command Button is supposed to clear the Check Box. This code is shown here:
Private Sub CommandButton1_Click()
Application.EnableEvents = False
Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = False
Application.EnableEvents = True
End Sub
This is the part that is strange (and maybe a bug?) When the value of the check box is changed programmatically, by clicking the button, the Check Box Click event code is executed! (This is evidenced by the facts that the value in cell B2 changes from 5 to 1 if the Check Box had been checked and that a breakpoint set in this code gets hit.) This occurs even with EnableEvents set to false!.
This errant behavior is causing me all kinds of problems. Can you offer any suggestions to solve this problem?
Thank you.
When the Check Box is checked, the value 5 appears in cell B2. When it is clear (clicked again), the value 1 appears in cell B2. The sophisticated code to make this work is shown here:
Private Sub CheckBox1_Click()
If (Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = False) Then
Range("B2") = 5
Else
Range("B2") = 1
End If
End Sub
The code attached to the Command Button is supposed to clear the Check Box. This code is shown here:
Private Sub CommandButton1_Click()
Application.EnableEvents = False
Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = False
Application.EnableEvents = True
End Sub
This is the part that is strange (and maybe a bug?) When the value of the check box is changed programmatically, by clicking the button, the Check Box Click event code is executed! (This is evidenced by the facts that the value in cell B2 changes from 5 to 1 if the Check Box had been checked and that a breakpoint set in this code gets hit.) This occurs even with EnableEvents set to false!.
This errant behavior is causing me all kinds of problems. Can you offer any suggestions to solve this problem?
Thank you.