Userform on top z-order?

[Rob]

Hi, I just have a quick question for you experts:

I'm designing an excel sheet for which users would be able to use a userform
to enter data. Now, having it enter data is easy, but I'm having a problem
bringing the userform to the top of all other windows.

At my work, we have what's called an "L-bar" around the edge of our screen,
which stays at the top of all our other windows, and limits the window space
we can use. I would like the userform to be able to be placed on top of this
"L-bar", in the top most z-order, so it won't take up any more of the very
little window space we have already. I tried making the userform modeless,
but it doesnt really help when excel itself cannot even go on top of this
"L-bar"!

Is there any way that I could make the userform (or any other data entry
form) have the highest z-order in the system?


Thank you,

-Robert G.

 

[JLGWhiz]

If the L-bar is not produced by Windows software, you might have a problem
trying to use VBA to override it.

 

[Rob]

I don't want to override the other program, I just want to bring this
userform to the top. Is there any way to do that?

 

[Chip Pearson]

Rob,

If you are showing the form modelessly (e.g, UserForm1.Show vbModeless), you
can use code in the form's code module like

Private Declare Function SetParent Lib "user32" ( _
ByVal hWndChild As Long, ByVal hWndNewParent As Long) As Long

Private Declare Function SetForegroundWindow Lib "user32" ( _
ByVal hwnd As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Sub UserForm_Initialize()
Dim XLHWnd As Long
Dim MeHWnd As Long

XLHWnd = Application.hwnd
MeHWnd = FindWindow("ThunderDFrame", Me.Caption)
If (MeHWnd > 0) And (XLHWnd > 0) Then
SetParent MeHWnd, XLHWnd
SetForegroundWindow (MeHWnd)
End If
End Sub

Note that this should be used ONLY if you are showing the form modelessly.
Do not use it if you are showing the form modally.

See www.cpearson.com/excel/SetParent.aspx for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)