Possible bug in ActiveX Check Box control?

[groston]

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.

 

[Legacy 98055]

Normal behaviour here. When you change the value of your checkbox it's default event is fired. Namely the click event. To get around this use the checkbox's mousedown event. The mousedown event will not be fired when it's value is changed programmatically. Also, the events of an activeX class are not Excel Application events. So Application.EnableEvents will have no effect on any activeX events.
<pre>
Private Sub CheckBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If (Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = False) Then
Range("B2") = 5
Else
Range("B2") = 1
End If
End Sub

Private Sub CommandButton1_Click()
Application.EnableEvents = False
Worksheets("Sheet1").CheckBox1.Value = False
Application.EnableEvents = True
End Sub

</pre>
tom

 

[dmckinney]

Hi Groston,

I don't see why the 'default event' should be triggered when you change the value programmatically. Some might call it a feature - I'd call it a bug. If you place the same controls on a user form, you won't get the same behaviour - why the difference?

In my opinion the problem lies with using the spreadsheet as the container for these controls. The behaviour on all sorts of occasions will differ from the behaviour you'd observe on the userform.

If you're doing anything anyway sophisticated, I'd strongly recommend you don't place these controls on the spreadsheet - rather you make a user form to contain the controls. Remember that with Excel 2000 you can have modeless user forms i.e. you still have access to the cells below while the userform is open.

Hope this helps

Dave.

(Example of a past problem I've encountered of this type (which remains unanswered!).

http://www.mrexcel.com/board/viewtopic.php?topic=14361&forum=2

http://www.mrexcel.com/board/viewtopic.php?topic=14234&forum=2

I ended up rewriting with all controls on userforms.
)

 

[Legacy 98055]

Hi Dave.
If I remember correctly, you did send me that workbook and the problem was solved?
This is in no way similiar to what happened with your workbook. That was a lot of code and you had references pointing to objects which had been destroyed if I recall correctly. The default event is better in most cases but I agree that it can be a pain. An easy workaround when dealing with multiple controls and their associated default events is to create a boolean flag, so to speak, which offers a similiar ability to the Application.EnableEvents = False or True. Use a Boolean in the general declarations section of your module to hold a value in between procedures and effectively exit the event. The first example could be solved using this method as such.
<pre>
Option Explicit

Dim ExitClickEvent As Boolean

Private Sub CheckBox1_Click()
If ExitClickEvent Then Exit Sub
If (Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = False) Then
Range("B2") = 5
Else
Range("B2") = 1
End If
End Sub

Private Sub CommandButton1_Click()
ExitClickEvent = True
Application.EnableEvents = False
Worksheets("Sheet1").OLEObjects("CheckBox1").Object.Value = False
Application.EnableEvents = True
ExitClickEvent = False
End Sub

</pre>
tom

 

[dmckinney]

Hi Tom,

I'd forgotten it was you who looked at my workbook. You proposed the only solution - which was none the less a workaround to avoid the buggy bizarre behaviour of the controls direct on the sheet.

However in the end I didn't implement your proposal, preferring to tackle what I consider the underlying problem which is using Excel as the container for the controls - which I find buggy to say the least.

I agree that the behaviour I witness bears no relation to the problem our friend is witnessing. Except insofar as I would consider it to be another manifestation of the same underlying problem i.e The same control inexplicably behaves differently when placed on the spreadsheet than when placed on a userform.

For this reason I personally choose to avoid placing the controls on the sheet, and would advise others to do the same. This is of course only my personal preference.

Finally I am very grateful for your input regarding my sheet problem. It seems that of late we've been brushing against each other on the board - it's not deliberate!

regards,

dave.

 

[Legacy 98055]

"brushing against?"
I say with!
Helping others solve their Office woes will not likely bring about world peace, but it's a healthy experience and I love alternative solutions. Let us continue brushing!
tom

 

[groston]

Thanks all for the thoughtful responses. I had considered making a userform, but since I have not yet worked with these, and since time was/is of the essence, I stayed with the method I understand.

I like the approach of using the boolean flag. Simple, yet effective.

I agree with Dave, however, that this firing of the default event is peculiar...