Application.EnableEvents = False not working

Hi all

I am trying to stop events as follows:

Application.EnableEvents = False
cboIssues.ListIndex = 1
Application.EnableEvents = True

The idea is that I don't want the Worksheet_Change() event to fire when
I set the ComboBox index. But as soon as I run the 2nd line, it goes
straight to the event. What am I not doing? I looked up help which
suggested I need an EventClassModule to instantiate the Application,
which I have done, but to no avail.

Any help appreciated.

Paul Martin
Melbourne, Australia

Hi Paul,
The Application.Enable events setting controls only Workbook and Worksheet
events. If you need to stop the code from executing in certain circumstances,
use a global variable. Set its value such as
gbExternal = true
<do things like setting the list index of the listbox/combobox etc>
gbExternal = false

and then in the actual event handler write
if gbExternal then exit sub


"Paul Martin" wrote:

> Hi all
>
> I am trying to stop events as follows:
>
> Application.EnableEvents = False
> cboIssues.ListIndex = 1
> Application.EnableEvents = True
>
> The idea is that I don't want the Worksheet_Change() event to fire when
> I set the ComboBox index. But as soon as I run the 2nd line, it goes
> straight to the event. What am I not doing? I looked up help which
> suggested I need an EventClassModule to instantiate the Application,
> which I have done, but to no avail.
>
> Any help appreciated.
>
> Paul Martin
> Melbourne, Australia
>
>

Hi Alok

Great, thanks.

Regards

Paul

In your Worksheet_Change() event, add the following If statement:

Sub Worksheet_Change()
if cboIssues.ListIndex = 1 then
' your existing code here
end if
end Sub

This will trigger the event only when the index is 1. Or put <>1 if you need
the oposit thing to happen.

- Mangesh



"Paul Martin" <pmartin1960@hotmail.com> wrote in message
news:1115690810.440174.190520@z14g2000cwz.googlegroups.com...
> Hi all
>
> I am trying to stop events as follows:
>
> Application.EnableEvents = False
> cboIssues.ListIndex = 1
> Application.EnableEvents = True
>
> The idea is that I don't want the Worksheet_Change() event to fire when
> I set the ComboBox index. But as soon as I run the 2nd line, it goes
> straight to the event. What am I not doing? I looked up help which
> suggested I need an EventClassModule to instantiate the Application,
> which I have done, but to no avail.
>
> Any help appreciated.
>
> Paul Martin
> Melbourne, Australia
>