Recalculating when an Excel sheet gets the focus from outside Excel

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

GS

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.

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
 
R

Rod Speed

If you mean switch to Excel from another window,

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.

OK, so how do I get the result I want ?
 
G

GS

GS said:
From a non Excel window, yes.


OK, so how do I get the result I want ?

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
 
R

Rod Speed

GS said:
Rod Speed wrote
You could 'toggle' the active sheet

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

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?

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?

No. It doesn't automatically recalc as the current clock time changes.
 
G

GS

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.

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
 
R

Rod Speed

I see! Thanks for further explaining your scenario. Pressing F9 doesn't
seem much of a hardship for me (I have Lou Gehrig's)

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?

Yeah, it is the most likely possibility.
 
R

Rod Speed

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.

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

GS

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.

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
 
M

Michael Bednarek

R

Rod Speed

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.

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.

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top