Trigger macro on leaving sheet

[one11]

Hi

I need pivot tables to be refreshed on leaving a specific sheet in my workbook. How do I trigger a macro on leaving a sheet ?

Currently, the refresh part is being done using a simple macro (refresh all) and the trigger is on a form button.

Thanks!
N

[6StringJazzer]

There is no event for leaving a worksheet, but the Worksheet.Activate event occurs if you enter a worksheet. I would recommend writing a sub in a Module that refreshes the pivot tables, then calling that from every worksheet like so:

Please Login or Register  to view this content.

May be impractical if you have many worksheets or constantly create new ones.

[6StringJazzer]

I thought about this a little more. What I suggested will do the refresh anytime you change worksheets, regardless of where you started from. If you really only want to refresh when you leave a particular worksheet, one solution is to keep a global variable in the Module that keeps track of what sheet you are in at any given time, then you can detect if you left a particular sheet when entering the next one.

That's pretty sketchy. If that's not clear I can provide some specific code to illustrate.

[6StringJazzer]

Well, here is the code. This code generates a message to the user upon leaving Sheet1. Note that there is code in every sheet to support this as described above.

[one11]

Hi 6StringJazzer

Twice you've come up and help in one week - much appreciated !

Cheers,
N

[shg]

There is no event for leaving a worksheet, ...

Please Login or Register  to view this content.

[6StringJazzer]

Oh, man. That's a demerit for me. Sorry.

My code works nonetheless. But here's the more elegant version.