Enable/Disable Worksheet Change Event code
 

I have worksheet_change event code stored in an addin.
The addin's routines are accessed via a series of menu items.

The event code is designed to track changes in Col B while
the user creates their workbook, or later returns to modify it.

Is there a way via a menu item to enable/disable the event
code, please?

So, the user would open a workbook and the event code
would be disabled. If the user wanted, they would select the
menu item to enable the event code (for that workbook only),
but when that workbook was closed/saved, then the event
code would be disabled by default.

Any help much appreciated.

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003

Enable/Disable Worksheet Change Event code
 

Stuart

I suspect you want the follwing line, but use it with caution as any errors
in the code could leave Excel with events turned of

Application.EnableEvents=True 'Switches events on

You can put the reverse

Application.EnableEvents=False

In the workbook_open event to turn them off each time the workbook is
opened.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS



Stuart wrote:
I have worksheet_change event code stored in an addin.
The addin's routines are accessed via a series of menu items.

The event code is designed to track changes in Col B while
the user creates their workbook, or later returns to modify it.

Is there a way via a menu item to enable/disable the event
code, please?

So, the user would open a workbook and the event code
would be disabled. If the user wanted, they would select the
menu item to enable the event code (for that workbook only),
but when that workbook was closed/saved, then the event
code would be disabled by default.

Any help much appreciated.

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003Stuart

Enable/Disable Worksheet Change Event code
 

Thanks for that. I see the need for caution.

The only thing I could think of was to have the menu
item load the event code into the ThisWorkbook
module of their chosen workbook, and then delete it
when the workbook is closed or saved, etc.

Might this be a safer way?

Regards.

"Nick Hodge" wrote in message
...
Stuart

I suspect you want the follwing line, but use it with caution as any
errors
in the code could leave Excel with events turned of

Application.EnableEvents=True 'Switches events on

You can put the reverse

Application.EnableEvents=False

In the workbook_open event to turn them off each time the workbook is
opened.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS



Stuart wrote:
I have worksheet_change event code stored in an addin.
The addin's routines are accessed via a series of menu items.

The event code is designed to track changes in Col B while
the user creates their workbook, or later returns to modify it.

Is there a way via a menu item to enable/disable the event
code, please?

So, the user would open a workbook and the event code
would be disabled. If the user wanted, they would select the
menu item to enable the event code (for that workbook only),
but when that workbook was closed/saved, then the event
code would be disabled by default.

Any help much appreciated.

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003Stuart




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003