Printable View
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.
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
Code:Private Sub UserForm_Activate()
Call KeepFormOnTop
End Sub
API Macro Code
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
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.
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.
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
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.
In that case you shouldn't progress on this path: it is too complicated for your level of 'expertise'.Quote:
Originally Posted by Silver
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
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
Code:' 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
Code: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
@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
Hello Mark,
You are correct about ShowWindow. This either hides or displays the application.
Hi Leith,
I downloaded ver2, mentioned time in column B and ran StartIt.... nothing happens.
Advise if I'm doing anything wrong
Hello Silver,
Sorry, I forgot to mention I haved to swap a few things around. Run "ShowForm".
Hi Leith,
Ran "ShowForm" as suggested, getting error as below -
Compile error : User-defined type not defined
and below code is highlighted -
Code:Public Declare Function ShowWindow _
Lib "user32.dll" _
(ByVal hwnd As LongPtr, _
ByVal nCmdShow As Long) _
As Long
Hello Siver,
What version of Windows are you using?
Hi Leith,
Using Windows 7 Ultimate. 64-bit operating system.
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.
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
Code: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
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.
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
@ GTO,
See post #15