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?
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;
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)
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.
Call SetWindowPos(hWndSelect, HWND_TOP, 0, 0, 0, 0, _
SWP_NOSIZE Or SWP_NOMOVE Or SWP_SHOWWINDOW)
Which calls the API defined as follows
' * 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
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
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
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.
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.