Hi,

I have created an Excel template used as a small application. Users
start the application by first hyperlinking to the directory where the
..xlt resides, and then by opening the .xlt file.

The problem arises when the Users open the .xlt. The Userform shows,
but underneath the directory window. The Users then have to minimize
the directory window to access the Userform. How annoying. Is there a
way to have the Userform load and show on top?

Any help you can lend would be appreciated.

Here is my start up code:
'------------------------------------------
Private Sub Workbook_Open()
Application.Visible = False
frmWelcome.Show
End Sub
'------------------------------------------
Henry

You can control which window appears "on top" using Windows API functions;
here is the code:
Declare Function FindWindow% Lib "user32" Alias "FindWindowA" _
(ByVal lpclassname As Any, _
ByVal lpCaption As Any)

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

' Constant for hWndInsertAfter
Global Const HWND_TOPMOST = -1 ' Puts the Window permanently on top of all
others

' Constants for wFlags:
Global Const SWP_NOSIZE = &H1 ' Do not resize the Window
Global Const SWP_NOMOVE = &H2 ' Do not move the Window

Sub TopMost(WindowTitle as String)

Dim hwnd%

hwnd% = FindWindow%(0&, WindowTitle)

Call SetWindowPos(hwnd%, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or
SWP_NOSIZE)

End Sub

WindowTitle needs to be the exact .Caption of the window you want to move to
the top, so the best way to implement the code is as follows:

UserForm1.Show
TopMost(UserForm1.Caption)

--
- K Dales


"Henry Stockbridge" wrote:

Hi,

I have created an Excel template used as a small application. Users
start the application by first hyperlinking to the directory where the
..xlt resides, and then by opening the .xlt file.

The problem arises when the Users open the .xlt. The Userform shows,
but underneath the directory window. The Users then have to minimize
the directory window to access the Userform. How annoying. Is there a
way to have the Userform load and show on top?

Any help you can lend would be appreciated.

Here is my start up code:
'------------------------------------------
Private Sub Workbook_Open()
Application.Visible = False
frmWelcome.Show
End Sub
'------------------------------------------
Henry

Hello,

Thanks for the code. The problem still persists. (Perhaps I placed
the code in the wrong two spots.)

I created a new module in the project for the code related to the API
functions. Then I added one line of code to the bottom of the
Worksheet Open event to reference the TopMost subroutine. When the
..xlt is opened, I see the flicker of the form, but it lands in the back
again. If I attempt to open it again, it lands on top.

Any other ideas?

Thanks,

Henry

Most likely the .xlt has it's own "TopMost" routine that overrides the one
you are setting.

There is a way to "force" your form to the front but I can't recall all the
coding details right now and afraid I don't have time to look them up. But
the method involves setting a timer and using the timer to continually
reposition your form as TopMost. You have to be careful with the timer
interval: too short will be constantly using your processor and too long will
lead to flicker. And if any other form uses this same method then there will
be conflicts and flicker. But it is the only way I know to keep the form on
top even if another app tries to make itself the topmost.
--
- K Dales


"Henry Stockbridge" wrote:

Hello,

Thanks for the code. The problem still persists. (Perhaps I placed
the code in the wrong two spots.)

I created a new module in the project for the code related to the API
functions. Then I added one line of code to the bottom of the
Worksheet Open event to reference the TopMost subroutine. When the
..xlt is opened, I see the flicker of the form, but it lands in the back
again. If I attempt to open it again, it lands on top.

Any other ideas?

Thanks,

Henry

Hi,

Thanks for your help. I did some further digging, and combined a
UserForm property with the API call you provided. Now all is well!

Application.Visible = False
Load frmWelcome
frmWelcome.StartUpPosition = 1
TopMost (frmWelcome.Caption)
frmWelcome.Show

Henry