focus

[Sammy]

Hi,

If I use application.sendmail or start another app from within excel,
excel looses focus...
I want excel to get focus again after the e-mail message (or app),
instead of sitting there "flashing" in de task-bar.

Can this be done ?

Sammy

[Jim Rech]

It used to be that you could use the API call SetForegroundWindow but MS
disable this in Windows 98/2000

You can try this workaround.

--
Jim Rech
Excel MVP

Public Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As
Long, lpdwProcessId As Long) As Long
Public Declare Function AttachThreadInput Lib "user32" (ByVal idAttach As
Long, ByVal idAttachTo As Long, ByVal fAttach As Long) As Long
Public Declare Function GetForegroundWindow Lib "user32" () As Long
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As
Long) As Long
Public Declare Function IsIconic Lib "user32" (ByVal hWnd As Long) As Long
Public Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal
nCmdShow As Long) As Long
Declare Function FindWindowA Lib "user32" (ByVal lpClassName As Long, ByVal
lpWindowName As String) As Long

Const SW_SHOW = 5
Const SW_RESTORE = 9

Sub Test() ''Start this macro then switch to another application
Application.OnTime Now + TimeValue("00:00:05"), "BringTotop"
End Sub

Sub BringToTop()
ForceForegroundWindow FindWindowA(0, Application.Caption)
End Sub

Function ForceForegroundWindow(ByVal hWnd As Long) As Boolean
Dim ThreadID1 As Long
Dim ThreadID2 As Long
Dim nRet As Long

If hWnd = GetForegroundWindow Then
ForceForegroundWindow = True
Else
ThreadID1 = GetWindowThreadProcessId(GetForegroundWindow, ByVal 0&)
ThreadID2 = GetWindowThreadProcessId(hWnd, ByVal 0&)
If ThreadID1 <> ThreadID2 Then
AttachThreadInput ThreadID1, ThreadID2, True
nRet = SetForegroundWindow(hWnd)
AttachThreadInput ThreadID1, ThreadID2, False
Else
nRet = SetForegroundWindow(hWnd)
End If
If IsIconic(hWnd) Then
ShowWindow hWnd, SW_RESTORE
Else
ShowWindow hWnd, SW_SHOW
End If
ForceForegroundWindow = CBool(nRet)
End If
End Function

[Tom Ogilvy]

Jim,
Is AppActivate unreliable in the situation described? (Recognizing that it
does not correct for a minimized window)

Sub Backontop()
Application.OnTime Now + TimeValue("00:00:15"), "Gototop"
End Sub

Sub gototop()
AppActivate "Microsoft Excel"
End Sub

Regards,
Tom Ogilvy

[Sammy]

Thanks, i'll give it go...

(come to think i thougt I oversaw something simple :-) )

Sammy

Jim Rech <jar...@kpmg.com> schreef in berichtnieuws
u00EKddj$GA.262@cppssbbsa04...

[Sammy]

Tom,

putting AppActivate "Microsoft Excel" after the
application.dialogs(xldialogssendmail).show was
all it took ! Simple yet wonderfull !

Thanks,

Sammy

Tom Ogilvy <Thomas....@hqda.army.mil> schreef in berichtnieuws
uev2MJej$GA.162@cppssbbsa03...

[Jim Rech]

Hi Tom-

AppActivate doesn't work for me under Windows 98 (nor, I've read, under
Windows 2000). It just causes Excel to flash in the task bar. What OS are
you using?

[Tom Ogilvy]

Jim,

The venerable Windows 95 here at work I have Win 98 second edition at
home - I will test it there. I guess it must use the API you mentioned.

Your approach seems much simpler anyway <bg>

Regards,
Tom

Jim Rech wrote in message ...

[Tom Ogilvy]

See Jim's caution on not working with Win 98 and Win NT if that is a concern
(and probably will be sooner or later if not now).

Regards,
Tom Ogilvy

Sammy wrote in message ...

[Tom Ogilvy]

Jim,
Tested it in Windows 98 SE and had the results you describe. The entry in
the taskbar flashed about 3 times - the application didn't come to the
front.

Regards,
Tom Ogilvy

Tom Ogilvy <Thomas....@hqda.army.mil> wrote in message
news:eaWJlrfj$GA.261@cppssbbsa05...

[Sammy]

Tom Ogilvy <Thomas....@hqda.army.mil> schreef in berichtnieuws

uF0Desfj$GA.255@cppssbbsa05...

> See Jim's caution on not working with Win 98 and Win NT if that is a
concern
> (and probably will be sooner or later if not now).

Well I am running win98SE and it works like a charm !

Thanks,

Sammy

[Jim Rech]

I read somewhere that MS's current thinking (as of a year ago) was that an
app shouldn't steal the focus from another app. So they changed the
behavior of certain APIs including apparently the ones used by AppActivate.
So you can still do it but it's just harder.

Jim