Run macro when excel workbook gains focus

[Perry]

I want to be able to run a macro in a spreadsheet (which is open but
out of focus) when the user re-select Excel as the active application.

I've tried Excel Application Events
Public WithEvents App As Application etc
but this only works when first opening Excel or a Workbook or oggling
between sheets.

As I say, I want to have an excel book open and every time the user
moves into Excel (with the sheet already in "view") from say Outlook or
any other application - Excel receing control - the Macro would run.

Can anyone help?

Thanks

 

[NickHK]

Perry,
Have you tried the App_WindowActivate or WorkbookActivate events ?

NickHK

 

[Perry]

Both only work opeing Excel for the first time or toggling/changing
focus between workbooks.

Neither are triggered when returning to Excel (with a Workbook open)
from another application.

Perhaps it's not possible? Although everything appears to indicate
Excel is active and in focus, it seems that the user needs still to do
something in the application for it to actually receive any event
trigger - toggling to is from another application is not enough.

 

[NickHK]

Perry,
OK, I see what you mean. Looks like you will have to resort to asking
Windows when Excel regains the foreground:

http://groups.google.co.uk/group/mi...61bb01ed397/bc21c9444e0d5012#bc21c9444e0d5012

http://discuss.joelonsoftware.com/default.asp?design.4.115661.7

NickHK

 

[Perry]

Yes, it seems that interegating windows is the only way of determining
which application is in focus. And at there appears to be no way Excel
knows itself, even the use of timers etc, these would need some kind of
"action" to be perfomed on the workbook for it to "wake-up". Use of
any timer OnTime would only become live once an event is triggered and
we've already determined that toggling into Excel does nothing.
Reading both items the only work around would appear to be to have
running a separate App, which when Excel is in focus somehow triggers
an event in Excel, which in turn triggers the macro I want run.
I think it all falls into the "too difficult" pile when a simple click
of a VB button will run the macro.
Shame really, just another example of the falabilities/shortcomings of
the product set.
If you think of anything in the meantime, drop me a line.
Cheers & thanks
P

 

[Perry]

On further observation, the OnTime method (I guess like all Methods)
does not remain, pending return focus to Excel if another application
is executed between start and scheduled time.

OnTime talks about waiting on a function completing and then the OnTime
function/macro running. This would appear only to be correct if Excel
remains the active application and has not been "deactiviated", to say,
check Outlook in the mean time.