Keep a UserForm on Top of All Other Windows

[Wombat18]

Hi

Firstly a great big thank-you for the help that everyone has provided in the past. I used to think my excel skills were OK until I started working with userforms but I now realise that I have a long way to go.

Only one way to learn however and that's to get in and give it a go. My current problem ...

I need to enter data from various WEB sites onto a worksheet and at the moment I'm toggling between each web site and the worksheet. What I would like to do is set up a userform which I can enter data from each page into and when finished hit a commandbutton to transfer it to the worksheet.

I'm mostly OK with creating the userform but not sure what code, and where I need to add it, to keep the userform on top of all other windows.

Also not sure if it's possible, I have another worksheet that has a series of hyperlinks that when selected open a web page, I can create a combobox that shows the the display names of the hyperlinks but not sure what code I need to add a commandbutton or some other control that will open the hyperlink.

Hope that all makes sense.

 

[Andrew Poulsom]

Maybe this will help you:

http://www.mrexcel.com/forum/excel-questions/386643-userform-always-top.html

 

[Wombat18]

I had come across this whilst searching for an answer and before posting my question. The problem (I think) is I'm using a 64bit machine and I think this code is specific to a 32bit machine. My skills are not that great so was unable to modify to suit (though I did try). What I would ideally like however is something that works on both 32 and 64 bit machines (i have a 64bit machine at home but only 32bit at work)

 

[Andrew Poulsom]

Here's another link:

Excel: Declaring API functions in 64 bit Office

 

[Wombat18]

Thanks for the hints...

I've successfully modified the code for 64bit PC (quite simple thanks to your hints) and I've put this code in a new standard Module

Option Explicit


Public Const SWP_NOMOVE = &H2
Public Const SWP_NOSIZE = &H1


Public Const HWND_TOP = 0
Public Const HWND_BOTTOM = 1
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2


Public Declare PtrSafe Function SetWindowPos Lib "user32" _
(ByVal hWnd As LongPtr, _
ByVal hWndInsertAfter As LongPtr, _
ByVal X As LongPtr, _
ByVal Y As LongPtr, _
ByVal cx As LongPtr, _
ByVal cy As LongPtr, _
ByVal uFlags As LongPtr) As Long


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


I still had a problem because even though the userform appeared on top of all other windows, as soon as I entered data into a textbox the focus moved off the page I was viewing back to excel and I had to toggle back to the page to view the data I need to enter into the next textbox.

I worked out however that if I added Application.WindowState = xlMinimized to the userform everything works a treat.

If anyone else is having the same problem and is reading this thread the full code I entered into the userform is ..

Option Explicit


Private Sub UserForm_Initialize()


Const C_VBA6_USERFORM_CLASSNAME = "ThunderDFrame"

Dim ret As Long
Dim formHWnd As Long

'Get window handle of the userform

formHWnd = FindWindow(C_VBA6_USERFORM_CLASSNAME, Me.caption)
If formHWnd = 0 Then
Debug.Print Err.LastDllError
End If


'Set userform window to 'always on top'

ret = SetWindowPos(formHWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE)
If ret = 0 Then
Debug.Print Err.LastDllError
End If

Application.WindowState = xlMinimized

End Sub

Again thanks for the help

PS. Any hints on how to resolve the second part of the problem I was having .. I have another worksheet that has a series of hyperlinks that when selected open a web page, I can create a combobox that shows the the display names of the hyperlinks but not sure what code I need to add a commandbutton or some other control that will open the hyperlink in a new window.

Have tried various variations of Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True but no luck.

 

[Andrew Poulsom]

Try:

ThisWorkbook.FollowHyperlink ComboBox1.Value