Keep a UserForm on Top of All Other Windows

Wombat18

New Member
Joined
Sep 6, 2013
Messages
13
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Wombat18

New Member
Joined
Sep 6, 2013
Messages
13
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)
 

Wombat18

New Member
Joined
Sep 6, 2013
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,201
Messages
5,640,819
Members
417,168
Latest member
StumpoC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top