Make userform stay on top of all windows when macro is fired

[Silver]

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]

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]

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]

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]

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]

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]

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]

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]

@Leith Ross:

Hi Leith

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]

Hello Mark,

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

[Silver]

Hi Leith,

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

Advise if I'm doing anything wrong

[Leith Ross]

Hello Silver,

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

[Silver]

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]

Hello Siver,

What version of Windows are you using?

[Silver]

Hi Leith,

Using Windows 7 Ultimate. 64-bit operating system.

[Leith Ross]

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]

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]

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]

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]

@ GTO,

See post #15