Toggle Macro On/Off

[isly168]

I use the following macro to fill cell C2 with the active cell value. Is there a way to toggle this on/off with a hotkey or other macro? It's tedious to open VB, delete the code, then re-enter it.

Please Login or Register  to view this content.

[Marc L]

One way is to deactivate any event via the EnableEvents property …

[Rick Rothstein]

Add this line of code at the top of the code module that your posted code is in (this line of code should be by itself, that is, NOT inside of any code procedures)...

Please Login or Register  to view this content.

and put the following code in the same module, but it location is not critical...

Please Login or Register  to view this content.

Now, double clicking the C2 cell will toggle whether cell C2 updates or not.

[isly168]

This is how I have it at it won't toggle off when I double-click on C2. Sorry if I'm doing something dumb, I'm a beginner.

Please Login or Register  to view this content.

[Rick Rothstein]

Sorry... my fault... I forgot to give you the change you have to make to your posted code . It should be changed to look like this...

Please Login or Register  to view this content.

The arrangement of the code as you posted it is correct... once you make the above change to your SelectionChange event code, everything should work fine.

[isly168]

Hmm, still no good. Now it's as if it's toggled off but double-clicking C2 won't get toggle it back on. C2 is a merged cell with C2/C3, could that be a problem?

Please Login or Register  to view this content.

[Rick Rothstein]

Yes, of course, Merged Cells are always a problem when code has to work around them. Here is a modification to the BeforeClick event code that should make everything work correctly for you...

Please Login or Register  to view this content.

Note: It is best to avoid using Merged Cells if possible as they are always a problem when code is involved; however, if you must use it, then you must mention it when posting future forum questions.

[isly168]

Perfect! Works great. Thanks Rick. Noted about the merged cells.