I have a workbook with multiple sheets. Two of the sheets each have a
different VBA function in several Cells. When I switch between the sheets,
the values returned by the function are gone and a "Calculate" has to be
done. I have Application.Volatile turned on, otherwise I have PF9 everytime.

How can I preserve the value when I switch between sheets?

Thank you....

"Bruce A. Julseth" wrote in message
...
I have a workbook with multiple sheets. Two of the sheets each have a
different VBA function in several Cells. When I switch between the sheets,
the values returned by the function are gone and a "Calculate" has to be
done. I have Application.Volatile turned on, otherwise I have PF9
everytime.

How can I preserve the value when I switch between sheets?

Thank you....


I haven't gotten a response to my problem yet and can help but wonder why?

1. Is the answer so obvious no one wants to take the time to answer? Not to
me!
2. Is it ambiguous? If so, tell me why and I'll be happy enhance the query.
3. No one knows!! I doubt that. A lot of you guy's are super sharp!!
4. Or something else.

Appreciate some kind of feedback.

Thanks......

"Gone" means what?

Values disappear or?

Any event code behind the sheets like Activate or Deactivate events?

Post the Functions and where they are entered on the sheets.


Gord Dibben MS Excel MVP

On Wed, 2 Dec 2009 13:03:03 -0500, "Bruce A. Julseth"
wrote:


"Bruce A. Julseth" wrote in message
...
I have a workbook with multiple sheets. Two of the sheets each have a
different VBA function in several Cells. When I switch between the sheets,
the values returned by the function are gone and a "Calculate" has to be
done. I have Application.Volatile turned on, otherwise I have PF9
everytime.

How can I preserve the value when I switch between sheets?

Thank you....


I haven't gotten a response to my problem yet and can help but wonder why?

1. Is the answer so obvious no one wants to take the time to answer? Not to
me!
2. Is it ambiguous? If so, tell me why and I'll be happy enhance the query.
3. No one knows!! I doubt that. A lot of you guy's are super sharp!!
4. Or something else.

Appreciate some kind of feedback.

Thanks......

Thank you for the response.

Gone means that cells that should have data in them are blank. The values
disappear. Recalulation happens when I switch since I have
Application.Volitile in the sheet and the values reappear. My question is
"Is there a way to stop them from disappearing so that the recalcuation
doesn't have to happen.

Each sheet has a function and two event handlers. The functions are
dependent upon data previously entered on the sheet and both are "almost"
then same. I'd have to send you the complete workbook for you to see them
operate. The following is one of the functions and the event handlers for
one of the sheets.

I have sent the spreadsheet directly to your id..

Well I deserve $50 for registering on this (&^%$$ system. May be I am having a bad day.

Your vba code probably refers to Activesheet. When you recalculate you are probably on another sheet. Pass a variable and use variable.worksheet

Cheers

Adam