Always go back Excel window when OnTime procedure is executing 1

[hoki]

Hi, everyone,
I've been trying making an Excel VBA procedure which uses ontime method to run itself periodically. This procedure will check the data and pop-up a remind window (UserForm) showing all items are due.

When this procedure is executing, I may be under the environment of IE or Outlook instead of staying in Excel. Is there an method to switch the "Focus" from current active application back to excel, so that I could see this message timely instead of waiting for my go back excel to see it?

I hope the feeling is at least like ICQ or MSN that no matter which application you are using, you will see the blink icon indicating there is a new message coming.

I've tried searching the archive and can't find this kind of info, so please kindly give me a hand on this issue.

Thanks a lot.

Hoki

 

[hoki]

update my experiment situation:

I found the method of "appactivate" and saw many people discussing it. Looks like it should be able to solve my problem.

But I do a little experiment as below:

1. open a new workbook,
2. and add a userform called UserForm1.
3. add a module and edit two sub as below-

Sub test()
Application.OnTime Now + TimeValue("00:01:00&quot, "xxx"
End Sub

Sub xxx()
AppActivate ("Microsoft Excel&quot, 3
UserForm1.Show
End Sub

Then I run test(), which supposed to execute xxx() one minute later.

Then I go browsing so that shifting the Focus from Excel to IE.

But the Focus doesn't go back to Excel anymore. After 10 minutes, I go back Excel and see UserForm1 showing.

So I still don't know how to make it happens.

Hope this could explain my question better for your understanding.

Best Regards,
Hoki

 

[hoki]

I guess this question is too easy for all friends here to answer, so I didn't get a response.
After more research and I think windows API could help, though I don't learn any of them. Finally I found a sample which I could apply after a little modification, and now it works.
For people who might also want to know this, I paste this info here:
=====================================================
Setting Excel to "Always on Top"

The following code example shows how to make Microsoft Excel "Always on Top". This prevents other applications
from being displayed in front of Microsoft Excel.

Option Explicit
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function SetWindowPos Lib "user32" (ByVal hWnd As Long, _
ByVal hWndInsertAfter As Long, _
ByVal x As Long, _
ByVal y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long) As Long
Global Const HWND_TOPMOST = -1
Global Const HWND_NOTOPMOST = -2

Sub SetOnTop()
Dim WinHnd As Long, SUCCESS As Long
WinHnd = FindWindow("xlmain", Application.Caption)
SUCCESS = SetWindowPos(WinHnd, HWND_TOPMOST, 0, 0, 0, 0, Flags)
'The following line is here just to switch Excel back to normal operation
'after 20 seconds
Application.OnTime Now + TimeValue("00:00:20&quot, "NotOnTop"
End Sub

Sub NotOnTop()
Dim WinHnd As Long, SUCCESS As Long
WinHnd = FindWindow("xlmain", Application.Caption)
SUCCESS = SetWindowPos(WinHnd, HWND_NOTOPMOST, 0, 0, 0, 0, Flags)
End Sub
===============================================
And the original document is here:

http://www.nordurmjolk.is/upload/files/SAMPLES.XLS

Thanks anyway,
Best Regards,
Hoki

 

[TonyJollans]

Hi hoki,

I didn't know the answer to your question, and it seemed no-one else around at the time did either. You went away and found out for yourself - well done! Having found your answer, you came back and posted it here for the benefit of all - many thanks, and a star, for that.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[hoki]

Hi Tony,
Thanks a lot for your warm response which let me make sure that someone do read my question but just can't help on it yet. And thanks for your star.

Regards,
Hoki