use a macro to temporarily stop Sub worksheet_activate()

[still learning]

Hi
Can I make a macro like a toggle button that will stop a Sub worksheet_activate macro?
I have a worksheet that i sometimes have to copy and paste to it or from it and every time i go back to it, i'm in the wrong place.
what i do now is put a ' on each line, then when i'm ready to save the sheet, i delete the '
I would like to put an icon on the ribbon.
The thing i don't how to do is write the macro.
I only want to deable the activate macro for the sheet i'm working on (not the whole workbook)

mike

 

[HaHoBe]

Hi stilll earnin,

what about using

Application.EnableEvents = False

to stop the events at the beginning of the macro and

Application.EnableEvents = True

at the end. Another possibility would be to use a checkbox on the sheet and evaluate from there: checked will run the event, uncheck omit it.

Ciao,
Holger

 

[still learning]

Hi HaHoBe
I've used that code before and it seems to disable all the workbooks. i only need to disable on sheet
May be I was putting it in the wrong place.

mike

 

[Fluff]

what i do now is put a ' on each line, then when i'm ready to save the sheet, i delete the '

Are you doing this manually for each line individually?

 

[still learning]

Hi Fluff
yep that's why i was wondering if i can make a macro.
I only want to for the one sheet, not the whole workbook.

mike

 

[Fluff]

Rather than commenting out the lines one by one you can just select the entire code & click the "Comment Block" icon in the Editor

 

[HaHoBe]

Hi Mike,

an alzernative to commenting the code might be to add these lines at the top of the the Worksheet-Activate code

...
Const blnStopEvent As Boolean = False

If blnStopEvent Then Exit Sub
...

As long as the constant shows False the code will be executed, on True no event will be raised.

I have a worksheet that i sometimes have to copy and paste to it or from it and every time i go back to it, i'm in the wrong place.

Can´t you stay away from changing sheets by fully qualifing the ranges you are working with?

Ciao,
Holger

 

[still learning]

I'll try both
Thank you

mike