Bringing a Program Window to the Front - Xtreme Visual Basic Talk

07-10-2007, 08:14 PM

cwcookman

Junior Contributor

Join Date: Dec 2004

Posts: 207

Bringing a Program Window to the Front

When using an Excel VBA macro to control another program is it possible to force the other programs window to the front of Excel and place the Excel window behind when the Excel macro finishes? I dont want to minimize the Excel window. I only want it moved behind the other window.

I am controlling a drafting program (TurboCad) with Excel VBA but I think the VBA code generally should be the similar to the VBA code to do the same thing with Word or Access.

Does anyone have an idea how to accomplish the task, with Word or Access if not TurboCad?

Any help would be appreciated.

07-11-2007, 07:00 PM

cwcookman

Junior Contributor

Join Date: Dec 2004

Posts: 207

I have not been able to bring the other program to the front but the following works in debug to push the Excel window to the back;

Code:

In the Declares Section:

Const HWND_BOTTOM = 1
Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const SWP_NOACTIVATE = &H10
Const SWP_SHOWWINDOW = &H40

Public hWND as Long

Private Declare Sub 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)


In the body of the code:

hWND = FindWindow("XLMAIN", 0)
Debug.Print hWND
SetWindowPos hWND, HWND_BOTTOM, 0, 0, 0, 0, SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE

however, once its run from a button the Excel window returns to the top when the macro exits.

Does anyone have any ideas?

07-17-2007, 08:55 PM

cwcookman

Junior Contributor

Join Date: Dec 2004

Posts: 207

I still haven't figured this out. It looks like even though the other program is put in focus the focus is returned to Excel when the macro exits.

I have decided an alternative will be to use the Excel VBA code to place the mouse arrow over the other programs button on the taskbar.

Does anyone have an idea how we can use VBA code (probably with some API calls) to place the mouse arrow over the other programs button on the taskbar?

Any help would be appreciated.

07-18-2007, 12:33 AM

LeifGund

Contributor

Join Date: Feb 2004

Location: Copenhagen, Denmark

Posts: 481

I have used the following code:

Code:

Call SetWindowPos(hWndSelect, HWND_TOP, 0, 0, 0, 0, _
            SWP_NOSIZE Or SWP_NOMOVE Or SWP_SHOWWINDOW)

Which calls the API defined as follows

Code:

...
'/*
' * ShowWindow() api public constants
' */
    Public Const SW_HIDE = 0
    Public Const SW_SHOWNORMAL = 1
    Public Const SW_NORMAL = 1
    Public Const SW_SHOWMINIMIZED = 2
    Public Const SW_SHOWMAXIMIZED = 3
    Public Const SW_MAXIMIZE = 3
    Public Const SW_SHOWNOACTIVATE = 4
    Public Const SW_SHOW = 5
    Public Const SW_MINIMIZE = 6
    Public Const SW_SHOWMINNOACTIVE = 7
    Public Const SW_SHOWNA = 8
    Public Const SW_RESTORE = 9
    Public Const SW_SHOWDEFAULT = 10
    Public Const SW_FORCEMINIMIZE = 11
    Public Const SW_MAX = 11
    Public Const WS_CHILD = &H40000000
...
    Public Const HWND_TOPMOST = -1
    Public Const HWND_NOTOPMOST = -2
    Public Const HWND_TOP = 0
    Public Const HWND_BOTTOM = 1

....
' Declare
    Public Declare Sub 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)

__________________
Why, a four-year-old child could understand this. (Run out and find me a four-year-old child; I can't make head or tail out of it.) - Groucho Marx

07-18-2007, 07:29 PM

cwcookman

Junior Contributor

Join Date: Dec 2004

Posts: 207

Leif,

How does what you posted differ from my second post?

07-18-2007, 07:51 PM

Roger_Wgnr Roger_Wgnr is offline

CodeASaurus Hex

Forum Leader
* Expert *

Join Date: Jul 2006

Location: San Antonio TX

Posts: 2,427

Quote:

Originally Posted by cwcookman

Leif,

How does what you posted differ from my second post?

Well the one thing I see that he did was use HWND_TOP instead of HWND_BOTTOM.
He also did not use SWP_NOACTIVATE

These diferences may have alteered the behavior of the window.

Just speculating but did see the differences in the code.

__________________
Code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. ~Martin Golding
The user is a peripheral that types when you issue a read request. ~Peter Williams
MSDN Visual Basic .NET General FAQ

07-18-2007, 08:24 PM

cwcookman

Junior Contributor

Join Date: Dec 2004

Posts: 207

I tried bringing the other window to the from using HWND_TOP and it didnt work but pushing Excel to the bottom using HWND_BOTTOM did work.

I have had a bit of success. Originally I was tagging the SetWindowPos code at the end of my button click event and Excel returned to the top when the macro exited. I moved the SetWindwPos code to the end of the Form Terminate event and now Excel goes to the bottom.

For the other window, it made no difference whether I set the window to the TOP with HWND_TOP, excel stayed on top. Neither does setting or not setting the SWP_NOACTIVATE flag on the other window have an effect, I still have to click the other window to activate it but at least it ends up on top with Excel on the bottom.

ive tried
1)SetWindowPos of the other window to the top with the SWP_NOACTIVATE flag set and not set: it doesnt work
2)SetWindowPos of the Excel window to the bottom: it works
3)SetWindowPos of the Excel window to the bottom
SetWindowPos of the other window to the top with the SWP_NOACTIVATE flag set and not set: it acts just like 2)

At least now I dont have to search the tool bar for the other window button after the macro runs each time. It would be nice if the other window ended up active, it would save me a mouse click each time I run the tool.

07-21-2007, 06:49 PM

cwcookman

Junior Contributor

Join Date: Dec 2004

Posts: 207

Resolved

I found SetForegroundWindow will do what I need to do. I am no longer using SetWindowPos.

I set hwnd equal to the handle of the other program. It gets placed on top and set in focus.

The call to SetForegroundWindow was placed at the end of the UserForm terminate event.

Last edited by cwcookman; 07-21-2007 at 07:16 PM.