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

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.
--
p45cal


"Maury Markowitz" wrote:

> 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

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.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Maury Markowitz" wrote:

> 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

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)

"Maury Markowitz" <(E-Mail Removed)> wrote in
message news:8EBE245E-FABF-435D-A778-(E-Mail Removed)...
>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