PDA

View Full Version : Make userform stay on top of all windows when macro is fired



Silver
01-08-2017, 08:46 PM
Hi,

Excel newbie here.

I came across a code that makes userform pop-up at specified times in a column.

Need assistance as below -

1) When the macro is fired at the specified time, I want the userform to pop-up and remain on top of all applications.

2) Once the userform pops-up, I need a button on the userform which, when clicked, will close the pop-up window and take me back to the workbook from where the macro is run.

Have attached sheet for reference.

Any assistance will be appreciated.

Leith Ross
01-09-2017, 01:54 PM
Hello Silver,


The macro shown below has been added to the attached workbook. The macro is called from the UserForm's Activate event. So whenever your macro shows the form, it will always be on top of all other windows.


NOTE: This Code Will Not Work on Macs.


UserForm_Activate Event


Private Sub UserForm_Activate()
Call KeepFormOnTop
End Sub



API Macro Code


' Written: July 07, 2009
' Author: Leith Ross
' Summary: Keeps a UserForm, or any window on top of all other windows.
' Call this macro from the UserForm_Activate event code module.




' Returns the Window Handle of the Window
' that is accepting User input.
Private Declare Function GetForegroundWindow Lib "user32.dll" () As Long


Private Declare Function SetWindowPos _
Lib "user32.dll" _
(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


Sub KeepFormOnTop()

Dim ret As Long

Const HWND_TOPMOST As Long = -1
Const SWP_NOMOVE As Long = &H2
Const SWP_NOSIZE As Long = &H1

ret = SetWindowPos(GetForegroundWindow(), HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE + SWP_NOSIZE)


End Sub

Silver
01-09-2017, 11:24 PM
Hi Leith,

Thanks for responding.

Currently what is happening in Rem ver 1 is, when I run the macro, excel keeps blinking in the taskbar. When I click on excel the userform pop-ups and remains on top.

What I'm looking for is -
a) When the macro is activated at the specified time, the userform should pop-up in front of all applications.
b) Clicking a button on the userform (say "Return") should return me to the workbook from where the macro is run.

How the macro should work -

1) Specify time in column B - let's say 11:45 (Time specified will be system time)

2) Alt + F8 and click on StartIt

3) When system time is 11:45, userform should pop-up in front of all applications

4) Clicking on "Return" will take me back to workbook from where the macro is run
(in this case it will be workbook "Rem ver 1")

Since I'll be working on too many applications, I want a reminder to pop-up in front of all applications at the specified time.

Leith Ross
01-09-2017, 11:56 PM
Hello Silver,

The code works correctly. What is happening is Excel has been minimized and is longer the active window. That is why it is blinking on the taskbar after the macro fires. You need some more code to make Excel the active window when the macro runs and display the UserForm on top of all the other windows. After the UserForm has been displayed, the Excel application can either remain open or be minimized to the taskbar.

Silver
01-10-2017, 12:27 AM
Hello Leith,

Sorry If I'm asking too much, Just an excel beginner.

I would be grateful, if you can provide me with just the code that would make the userform pop-up at the specified time. (To be specific Point 1 to 3 from my previous post will be enough)

Kindly help me out

Leith Ross
01-10-2017, 01:14 AM
Hello Silver,

I will work on this and test it before I post back. It is after midnight my time. I will resume working on this in the morning.

snb
01-10-2017, 01:30 AM
Hello Leith,

Just an excel beginner.



In that case you shouldn't progress on this path: it is too complicated for your level of 'expertise'.

Leith Ross
01-10-2017, 02:17 PM
Hello Silver,

This worked for me. Here is the code for the UserForm and the Modules. The attached workbook has all these changes.


UserForm code


Private Sub CommandButton1_Click()
Unload Me
End Sub


Private Sub UserForm_Activate()
Label1.Caption = "Next Show Time: " & Module1.dtmNextTime
Call KeepFormOnTop
End Sub


Private Sub UserForm_Initialize()
Keep_Form_On_Top.xlHwnd = Excel.Application.hwnd
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim ret As Long
ret = ShowWindow(xlHwnd, 1)
End Sub



Module - Keep_Form_On_Top


' This variable is initalized in the UserForm_Initialize() event. It holds the hWnd to the Excel Application Window.
Global xlHwnd As Long


' This API call is used to hide or show the Excel Application.
Public Declare Function ShowWindow _
Lib "user32.dll" _
(ByVal hwnd As LongPtr, _
ByVal nCmdShow As Long) _
As Long


' Returns the Window Handle of the Window that is accepting User input.
Private Declare Function GetForegroundWindow Lib "user32.dll" () As Long


Private Declare Function SetWindowPos _
Lib "user32.dll" _
(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


Sub KeepFormOnTop()

Dim ret As Long

Const HWND_TOPMOST As Long = -1
Const SWP_NOMOVE As Long = &H2
Const SWP_NOSIZE As Long = &H1

ret = ShowWindow(xlHwnd, 0)
ret = SetWindowPos(GetForegroundWindow(), HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE + SWP_NOSIZE)


End Sub



Module - Module1


Global dtmNextTime As Date
Global lngRow As Long


Const strSheet = "Sheet1"


Sub StartIt()
lngRow = Evaluate("MATCH(NOW()-TODAY(),'[" & ThisWorkbook.Name & "]" & strSheet & "'!B:B)")
End Sub


Sub SetTimer()
lngRow = lngRow + 1
dtmNextTime = ThisWorkbook.Worksheets(strSheet).Range("B" & lngRow).Value
If dtmNextTime > 0 Then
Application.OnTime Date + dtmNextTime, "ShowForm"
End If
End Sub


Sub ShowForm()
Call StartIt
UserForm1.Show
SetTimer
End Sub

GTO
01-11-2017, 12:30 AM
@Leith Ross:

Hi Leith:hi:

Not sure if I am missing something, but at least in my current environment (Win7 64-bit; Excel 2010 installed in 32-bit mode), the ribbon disappears. Maybe ShowWindow causes?

Mark

Leith Ross
01-11-2017, 09:00 AM
Hello Mark,

You are correct about ShowWindow. This either hides or displays the application.

Silver
01-11-2017, 01:28 PM
Hi Leith,

I downloaded ver2, mentioned time in column B and ran StartIt.... nothing happens.

Advise if I'm doing anything wrong

Leith Ross
01-11-2017, 01:49 PM
Hello Silver,

Sorry, I forgot to mention I haved to swap a few things around. Run "ShowForm".

Silver
01-11-2017, 01:57 PM
Hi Leith,

Ran "ShowForm" as suggested, getting error as below -

Compile error : User-defined type not defined

and below code is highlighted -


Public Declare Function ShowWindow _
Lib "user32.dll" _
(ByVal hwnd As LongPtr, _
ByVal nCmdShow As Long) _
As Long

Leith Ross
01-11-2017, 02:06 PM
Hello Siver,

What version of Windows are you using?

Silver
01-11-2017, 02:13 PM
Hi Leith,

Using Windows 7 Ultimate. 64-bit operating system.

Leith Ross
01-11-2017, 03:31 PM
Hello Silver,

One of these workbooks should work for you. One has 32 bit API calls and the other has 64 bit API calls.

After you have setup your time in column "B", execute the "Run" macro. This name makes more sense.

Silver
01-12-2017, 12:06 AM
Hi Leith,

When I'm running 32 bit version, getting below error -

Run-time error '424':
Object required

and UserForm1.Show from below portion of code gets highlighted


Sub Run()
Call StartIt
UserForm1.Show
SetTimer
End Sub


When opening 64 bit version, excel goes directly into the coding window a throws pop-up -

Compile error:
Expected: Sub or Function

Below portion of the code is highlighted in RED -

' This API call is used to hide or show the Excel Application.
Public Declare PtrSafe Function ShowWindow _
Lib "user32.dll" _
(ByVal hwnd As LongPtr, _
ByVal nCmdShow As Long) _
As Long


' Returns the Window Handle of the Window that is accepting User input.
Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr

Private Declare PtrSafe Function SetWindowPos _
Lib "user32.dll" _
(ByVal hwnd As LongPtr, _
ByVal hWndInsertAfter As LongPtr, _
ByVal x As Long, _
ByVal Y As Long, _
ByVal cx As Long, _
ByVal cy As Long, _
ByVal wFlags As Long) _
As Long

Leith Ross
01-12-2017, 10:29 AM
Hello Silver,

The errors indicate to me that you are attempting to run this code on a Mac. This code will not run a Mac. The Mac does not support Windows API calls.

If you are running this on a PC then I have no idea what the problem is. Both codes work fine on my PC which is running Windows 7 with Office 2010.

GTO
01-12-2017, 10:49 PM
Hi Silver,

Maybe I am missing it, but besides Leith's concern of PC or Mac(?), I am not clear on what version of Excel you are using, and whether Excel is installed in 32-bit or 64-bit mode?

Mark

Leith Ross
01-13-2017, 03:03 PM
@ GTO,

See post #15 (http://www.vbaexpress.com/forum/showthread.php?58189-Make-userform-stay-on-top-of-all-windows-when-macro-is-fired&p=354813&viewfull=1#post354813)

Paul_Hossler
01-13-2017, 03:50 PM
OP is running Win7 Ultimate 64 bit (Post #15) with 32 bit Office (assuming from Post #17)

I added Option Explicit to Leith's workbook's -- Rem ver 32 bit.xlsb (Post #16) -- including the UserForm1, fixed some undefined references and changed Global to Public


Largest issue (and the one causing the 424 - Object Required error message - was in the initialization for the Userform1 since there was no module named 'Keep_Form_On_Top'

18030


Seems to run OK under Win10 (64) Office 2016 (32) - if it does what is wanted is another story

Bugs
04-06-2022, 07:55 AM
I am using excel 365 64 bit , and the code no longer works

thanks for the help

SamT
04-06-2022, 06:12 PM
MS Office UserForms are for local use. Office365 UserForms are for Intra/Inter Net sites.