Calling a module only when a sheet is actually "activated"

[Maury Markowitz]

I have some code that is triggered when the user selects a sheet within a
workbook. It redraws a menu, which changes from sheet to sheet.

The problem is that this takes some time to run, and when you're running
functions that cycle through the sheets (and we have lots of these) the delay
while the menu redraws actually becomes a significant time waster.

So what I'd like to do is have this code fire not when the workbood simply
changes tabs, but when the USER does this. Any ideas?

Maury

 

[p45cal]

2 suggestions:
1. 99% of code does not needto activate/select anything, so change your code
to reflect this (post a small section of your code and I/someone will show
you the equivalent).
2. Use Application.EnableEvents=False/True carefully.

 

[John Bundy]

Don't think you can trap for (or would want to ) when just the user changes
at the beginning of your code put in application.screenupdating=false and
change back to true at end, untested but i believe that will keep any menus
from drawing.

 

[Bob Phillips]

Just create a public variable which the worksheet event routine tests for,
and set and reset in your looping code, or even use
Application.EnableEvents.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)