How to show a Userform on top of other applications 1

[radubalmus]

I have a userform that opens with the workbook, and asks for user and password

Private Sub Workbook_Open()
    Application.WindowState = xlMinimized
    Application.Visible = False
    UserForm2.CommandButton1.Enabled = False
    UserForm2.Show
End Sub

The problem: When i have other applications opened(internet explorer, windows explorer or other program) if i open my workbook then the userform goes behind all opened applications. I have to minimize all to see the userform and type in user and pass.

Question: how do i set the userform to be shown on top of all opened applications(something like system modal)

Thanks in advance

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[combo]

You need API:

Const HWND_TOPMOST = -1
Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const SWP_NOACTIVATE = &H10
Const SWP_SHOWWINDOW = &H40
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) 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)

Private Sub UserForm_Activate()
    SetWindowPos FindWindow("ThunderDFrame", Me.Caption), HWND_TOPMOST, 0, 0, 0, 0, SWP_NOACTIVATE Or SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE
End Sub

combo

 

[rmikesmith]

Radubalmus,

You might try displaying the Userform as a non-modal form and see if that works for you (requires Excel 2000 or later):

Private Sub Workbook_Open()
    Application.WindowState = xlMinimized
    Application.Visible = False
    UserForm2.CommandButton1.Enabled = False
    UserForm2.Show [COLOR=red]False[/color]
End Sub

Regards,
Mike

 

[radubalmus]

thanks guys,

combo,
the API works like a charm(i was thinking i need API, from what i was reading, but i never use it before)

Where can study more about API(do you have some interesting links)????



There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[combo]

A good, but not updated AllApi site can be a starting point. A lot of code samples, esp. vb, use API support.

combo

 

[radubalmus]

thanks combo,
this helps a lot

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[combo]

As the userform has not hWnd property, I prefer to add it (with API) to simplify future coding:

Public hWnd As Long

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

Private Sub UserForm_Initialize()
hWnd = FindWindow("ThunderDFrame", Me.Caption)
End Sub

combo

 

[radubalmus]

you mean that you can't hadle the userform(drag it on the screen??) and with the last example posted you can move it???

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[combo]

You can move the form of course, but the window's hWnd can be required in many api functions. In case of userform, you can get it each time using FindWindow function - as you don't know the first call you need it. It is more useful to automate it on form startup procedure. hWnd declared as public Long appears as property, can be accessed next in the same way (Me.hWnd) or just hWnd inside the useform code module.

combo