EnableEvents doesn't work

[Guest]

Hi All,

I have a checkbox with a CheckBox_Click event. In some cases I want to
change the value of its linked cell (to FALSE) through VBA, and in these
cases I don't want to run CheckBox_Click event code. I set
Application.EnableEvents to FALSE before changing the linked cell value, but
event code is still executed. How can I avoid it?

Thanks,
Stefi

 

[Guest]

Or how can I differentiate real click and changing linked cell value with VBA?
Stefi


„Stefi†ezt írta:

 

[Norman Jones]

Hi Stefi,

EnableEvents does not work with these controls.

Try using a boolean variable, For example try
something like:

'=============>>
Private Sub CheckBox1_Click()
If Not blStop Then
'Do something, e.g.:
MsgBox "Hi"
End If
End Sub
'<<=============


In a standard module:
'=============>>
Public blStop As Boolean

Option Explicit

Public Sub Tester()
blStop = True
ActiveSheet.OLEObjects("CheckBox1").Object.Value = xlOn

End Sub
'<<=============

 

[Norman Jones]

Hi Stefi,

'--------------
Public blStop As Boolean

Option Explicit

'--------------


Was intended as

 

[Norman Jones]

Hi Stefi,

'--------------
Public blStop As Boolean

Option Explicit

'--------------


Was intended as

Option Explicit
Public blStop As Boolean

 

[Dave Peterson]

This isn't an event to excel. So you have to take care of it yourself.

Are these controls from the control toolbox toolbar placed on a worksheet?

If you're calling the _click event from a procedure in a general module, you can
use:

Option Explicit
Public blkproc As Boolean
Sub testme()
blkproc = True
Sheet1.CheckBox1.Value = False
blkproc = False
End Sub

Then in the worksheet module:
Option Explicit
Private Sub CheckBox1_Change()
If blkproc = True Then Exit Sub
MsgBox "Change"
End Sub
Private Sub CheckBox1_Click()
If blkproc = True Then Exit Sub
MsgBox "Click"
End Sub

(I usually use _change.)

 

[Guest]

Many thanks, Dave and Norman for your quick responses, I'll try that way!
Stefi


„Dave Peterson†ezt írta:

 

[Guest]

Now it works nicely, thanks!
Stefi


„Stefi†ezt írta: