Enable/Disable Worksheet Change Event code

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

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.
 
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
(e-mail address removed)
 
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 said:
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
(e-mail address removed)
Click to expand...
 
Back
Top