Display popup for a time period in Excel

Question

I am trying to generate a popup that closes after a given WaitTime in seconds.

I consulted this link and this link.

I tried to apply the method from "VBA Excel macro message box auto close"; my code is the following:

Sub TestSubroutine()

Dim TemporalBox As Integer
Dim WaitTime As Integer
Dim WScriptShell As Object

Set WScriptShell = CreateObject("WScript.Shell")

WaitTime = 1
TemporalBox = WScriptShell.Popup("The message box will close in 1 second.", _
WaitTime, "File processed")

End Sub

The popup is displayed but it never closes after one second.

Edit #1

Based on @Skip Intro comment, I have updated the code:

Sub TestSubroutine()

Dim WaitTime As Integer

WaitTime = 1
CreateObject("WScript.Shell").Popup "The message box will close in 1 second.", _
WaitTime, "File processed"

End Sub

However this does not solve the original issue, the popup does not close after 1 second.

Edit #2

This is the code suggested by @Glitch_Doctor, however it still doesn't work:

Sub TestSubroutine()

Dim TemporalBox As Integer
Dim WaitTime As Integer
Dim WScriptShell As Object
Dim test

Set WScriptShell = CreateObject("WScript.Shell")

WaitTime = 1
Select Case TemporalBox = WScriptShell.Popup("The message box will close in 1 second.", _
WaitTime, "File processed")
    Case 1, -1
End Select

End Sub

You can call a popup in one line: `CreateObject("WScript.Shell").popup "This is a message", 1, "This is a header", 0 + 64 '` It only gets complicated if you're trying to capture a response, which I don't think you want if `Wait` = 1 second — Skip Intro, Nov 16 '16 at 11:53
Thank you for the tip. However, this does not solve the original issue, the popup stays open. I have updated the original post. — Daneel Olivaw, Nov 16 '16 at 12:14
That second bit of code works for me. The box appears, then auto closes a second later. — Skip Intro, Nov 16 '16 at 13:49
What OS are you on? I am using Windows 7 and it works according to expectations — Robert J., Nov 16 '16 at 14:23
Hmm, your code, the Edit#1 and the Edit#2 code **all** work for me - `Windows 7` `Excel 2010` — Skip Intro, Nov 16 '16 at 15:12

score 4 · Accepted Answer · edited May 23 '17 at 12:24

4

I finally found a very simple solution - credits to @Orphid, see his answer in the following thread.

I did not solve the specific issue related to my original code, but I managed to create a PopUp that closes after a specified period of time. The code is the following:

Sub subClosingPopUp(PauseTime As Integer, Message As String, Title As String)

Dim WScriptShell As Object
Dim ConfigString As String

Set WScriptShell = CreateObject("WScript.Shell")
ConfigString = "mshta.exe vbscript:close(CreateObject(""WScript.Shell"")." & _
               "Popup(""" & Message & """," & PauseTime & ",""" & Title & """))"

WScriptShell.Run ConfigString

End Sub

This works just fine.

edited May 23 '17 at 12:24

Community

1
1

answered Nov 17 '16 at 16:08

Daneel Olivaw

2,077
4
15
23

Can we make this a Yes/No message box? And automatically make it press no after PauseTime if Yes is not pressed – Gangula Jan 11 '19 at 09:27
2

This has the advantage of not halting the code execution while the message is displayed – 6diegodiego9 Jun 20 '22 at 20:24

Robert J. · Answer 2 · 2016-11-16T14:32:56.767

Another approach (if your would not work at all).

Create a new userform named frm_Popup and add a label there named lbl_Message. Add the following void to userform code:

Public Sub StartProcess(iTime As Integer)
    Me.lbl_Message.Caption = "The message box will close in " & iTime & " second(s)."
End Sub

then in your module:

Sub ShowMessage()
    Dim iTimeToWait As Integer
        iTimeToWait = 2

    With frm_Popup
        .Show False
        Call .StartProcess(iTimeToWait)
    End With

    Application.OnTime Now + TimeValue("00:00:" & iTimeToWait), "HidePopup"
End Sub

Private Sub HidePopup()
    Unload frm_Popup
End Sub

I was thinking of going down the `Application.OnTime` route once I'd finished my report that I'm working on... Well played sir. — Glitch_Doctor, Nov 16 '16 at 14:37

score 0 · Answer 3 · answered Nov 16 '16 at 12:26

0

You're just missing the Select Case:

WaitTime = 1
Select Case TemporalBox = WScriptShell.Popup("The message box will close in 1 second.", _
WaitTime, "File processed")
    Case 1, -1
End Select

I tested and it works...

answered Nov 16 '16 at 12:26

Glitch_Doctor

2,994
3
16
30

I get a runtime `error 424: object required` when I try that code. – Skip Intro Nov 16 '16 at 13:54
Sorry I was using your original code, it works with those `Dim`'s – Glitch_Doctor Nov 16 '16 at 13:57
Weird, do you still get the error with the `Dim`'s in or is the `.Popup` just not closing? – Glitch_Doctor Nov 16 '16 at 14:22
I don't have any error, the popup is displayed but never closes. – Daneel Olivaw Nov 16 '16 at 14:28
Only other thing I could think of is that I have a reference turned on that you don't, but the other answer should work. Apart from internet controls which are 100% not relevant to this all I have on is: `Visual Basic For Applications`, `Microsoft Excel 14.0 Object Library`, `OLE Automation`, `Microsoft Office 14.0 Object Library`. Alas I can't differentiate between them. I've just turned them on at some point when I've needed to. – Glitch_Doctor Nov 16 '16 at 14:43
I have the same references turned on - except I have version 15.0 instead of 14.0, but I have tested the code in another computer with version 15.0 and it works. – Daneel Olivaw Nov 17 '16 at 11:24

score 0 · Answer 4 · edited Dec 27 '20 at 08:27

Below code work for me, I added a 2-sec delay before the popup message appears. After 4-sec it auto disappear. I learn it from Mr. Dinesh Kumar Takyar. He added a 5-sec delay b4 popup appears. His youtube link https://www.youtube.com/watch?v=x1nmqVRrq-Q&list=PLwC8syx0i_6nHjAogOm9m4oGBq40YHkXV&index=4 I think the key issue is you need a delay for the popup timer to work. Maybe the Excel application needs to run for a while b4 the popup appears.

Option Explicit

Const PopUpTime As Integer = 4

Sub ShellMessageBox()

Dim MsgBoxWithTimer As Integer

MsgBoxWithTimer=CreateObject("WScript.Shell").Popup("Put your message here", PopUpTime, _
"Notice!", 0)

End Sub

Sub startTimer()
Application.OnTime Now + TimeValue("00:00:02"), "ShellMessageBox"
End Sub

Private Sub Workbook_Open()

startTimer

End Sub

score -1 · Answer 5 · edited Jan 17 '20 at 12:18

-1

The following code works for me:

Sub TimeBasedPopUp()

Dim WaitTime As Integer

WaitTime = 1
Select Case CreateObject("WScript.Shell").Popup("The message box will close in 1 second.",_
WaitTime, "MS Excel")
Case 1, -1

End Select

End Sub

edited Jan 17 '20 at 12:18

matt freake

4,877
4
27
56

answered Jan 17 '20 at 06:32

Akshay Jadhav

1

Display popup for a time period in Excel

5 Answers5

Linked

Related