Recalculating when an Excel sheet gets the focus from outside Excel

  • Thread starter Thread starter Rod Speed
  • Start date Start date
R

Rod Speed

I want to be able to have the sheet automatically
recalculate when it gets the focus from outside Excel.

I have tried

Private Sub Worksheet_Activate()
Worksheets("progress").Calculate
End Sub

That does do a recalc when you change from one sheet
to another in an Excel 2003 workbook, but it doesn't
recalculate when you switch to Excel from outside Excel.
 
I want to be able to have the sheet automatically recalculate when it
gets the focus from outside Excel.

I have tried

Private Sub Worksheet_Activate()
Worksheets("progress").Calculate
End Sub

That does do a recalc when you change from one sheet to another in an
Excel 2003 workbook, but it doesn't recalculate when you switch to
Excel from outside Excel.
Click to expand...

If you mean switch to Excel from another window, the 'active' sheet is
the one previously 'activated' when you switched from Excel to another
window. Thus, it did not fire its _Activate event because it did not
_Deactivate when you left the Excel window.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
If you mean switch to Excel from another window,
Click to expand...

From a non Excel window, yes.
the 'active' sheet is the one previously 'activated'
when you switched from Excel to another window.
Thus, it did not fire its _Activate event because it did
not _Deactivate when you left the Excel window.
Click to expand...

OK, so how do I get the result I want ?
 
GS said:
From a non Excel window, yes.


OK, so how do I get the result I want ?
Click to expand...

You could 'toggle' the active sheet or just press F9. But why would you
want to force calc? Is automatic calc turned off?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS said:
Rod Speed wrote
Click to expand...
You could 'toggle' the active sheet
Click to expand...

You mean by clicking on a different one ? That's
a manual op that I am trying to automate.
or just press F9.
Click to expand...

That's what I have been doing up till now, but I want to have that done
auto.
But why would you want to force calc?
Click to expand...

That sheet has a calculation that is based on the current
clock time and I want it to recalculate when I switch
back to that sheet from a window outside Excel.
Is automatic calc turned off?
Click to expand...

No. It doesn't automatically recalc as the current clock time changes.
 
That sheet has a calculation that is based on the current
clock time and I want it to recalculate when I switch
back to that sheet from a window outside Excel.


No. It doesn't automatically recalc as the current clock time
changes.
Click to expand...

I see! Thanks for further explaining your scenario. Pressing F9 doesn't
seem much of a hardship for me (I have Lou Gehrig's) and so I've never
explored alternative ways. There is a 'WindowActivate' event for the
Application object but I've never used it and so can't advise you.
Perhaps a solution lies there?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I see! Thanks for further explaining your scenario. Pressing F9 doesn't
seem much of a hardship for me (I have Lou Gehrig's)
Click to expand...

Yeah, its just rather more elegant to have it
done auto as you switch to the excel window.
and so I've never explored alternative ways. There is a 'WindowActivate'
event for the Application object but I've never used it and so can't
advise you. Perhaps a solution lies there?
Click to expand...

Yeah, it is the most likely possibility.
 
Rod Speed said:
Yeah, its just rather more elegant to have it
done auto as you switch to the excel window.


Yeah, it is the most likely possibility.
Click to expand...

Tried that, makes no difference, get exactly the same result,
switching back to the Excel window from a non Excel window
doesn't see the recalc happen.
 
Tried that, makes no difference, get exactly the same result,
switching back to the Excel window from a non Excel window
doesn't see the recalc happen.
Click to expand...

Thanks for confirming!
So then the current active window persists until it gets deactivated
via the UI. I suspected as much since activate/deactivate *are* events
only triggered within Excel.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Michael Bednarek said:
[snip]
That sheet has a calculation that is based on the current
clock time and I want it to recalculate when I switch
back to that sheet from a window outside Excel.


No. It doesn't automatically recalc as the current clock time changes.
Click to expand...

There several ways of running a code triggered by a timer; see
<http://www.cpearson.com/excel/OnTime.aspx> &
<http://stackoverflow.com/questions/2319683/>, but they will of course
run permanently, with Excel active or not.
Click to expand...

Yeah, while I initially rejected that idea because its running all the time
even I am not interested in having the sheet recalculating, it does have
one other very powerful advantage that you can see the sheet recalculated
value ticking down with the sheet visible but not with the focus with the
focus on the other non excel window where you are using the recalculated
value mentally.

Would be easy to have a button the turns the timer
off and on so its only on when you need it on.
 
Back
Top