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

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


€žStefi€� ezt Ã*rta:

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

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
'<<=============

---
Regards,
Norman



"Stefi" wrote in message
...
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

Hi Stefi,

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

Option Explicit

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


Was intended as


--
---
Regards,
Norman

Hi Stefi,

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

Option Explicit

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


Was intended as

Option Explicit
Public blStop As Boolean

---
Regards,
Norman




"Norman Jones" wrote in message
...
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
'<<=============

---
Regards,
Norman



"Stefi" wrote in message
...
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

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.)




Stefi wrote:

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

--

Dave Peterson

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


€žDave Peterson€� ezt Ã*rta:

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.)




Stefi wrote:

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

--

Dave Peterson

Now it works nicely, thanks!
Stefi


€žStefi€� ezt Ã*rta:

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


€žDave Peterson€� ezt Ã*rta:

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.)




Stefi wrote:

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

--

Dave Peterson