RE: Displaying a Msgbox 1 hour after the workbook opens

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
567
RE: Displaying a Msgbox 1 hour after the workbook opens

This code is in the Workbook Open() event:
Code:
Private Sub Workbook_Open()
If Day(Now) = 10 Then
    MsgBox "Good morning"
else
End if
This works great but displays the MsgBox as soon as soon the workbook is opened. How do I get the MsgBox to appear 1 hour(or, to extend this to a more elaborate solution, any later time specified by an input variable) after the workbook is opened.

Thanks for anyone's help on this.
cr
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,646
Office Version
365
Platform
Windows
Re: Displaying a Msgbox 1 hour after the workbook opens

Posting your email address is not recommended unless you want to tempt spammers galore!

In ThisWorkbook module
Code:
Private Sub Workbook_Open()
    Dim dTime As Date, timeDelay As Date
    timeDelay = TimeValue("01:00:00")
    If Day(Now) = 10 Then
        dTime = Now + timeDelay
        Application.OnTime dTime, "[COLOR=#b22222]MyMacro[/COLOR]"
    End If
End Sub
In STANDARD module
Code:
Sub [COLOR=#b22222]MyMacro[/COLOR]()
    MsgBox "Hello"
End Sub
 

chazrab

Well-known Member
Joined
Oct 21, 2006
Messages
567
Re: Displaying a Msgbox 1 hour after the workbook opens

Posting your email address is not recommended unless you want to tempt spammers galore!

In ThisWorkbook module
Code:
Private Sub Workbook_Open()
    Dim dTime As Date, timeDelay As Date
    timeDelay = TimeValue("01:00:00")
    If Day(Now) = 10 Then
        dTime = Now + timeDelay
        Application.OnTime dTime, "[COLOR=#b22222]MyMacro[/COLOR]"
    End If
End Sub
In STANDARD module
Code:
Sub [COLOR=#b22222]MyMacro[/COLOR]()
    MsgBox "Hello"
End Sub
Thx for helping with this - you answered my issue correctly - display the Msgbox after 1 hour. But what if I wanted to specify ANY time value of timeDelay to use your variable name, from a combo dropdown list or a textbox on a userform within the same application. This timeDelay variable has to be formatted correctly to run the code. That's the issue I'm having now. Thx for any help.
cr
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,646
Office Version
365
Platform
Windows
Re: Displaying a Msgbox 1 hour after the workbook opens

Time is a number between 0 and 1 as follows
24 hours = 1
so ...
1 second = 1/(24*60*60)
6 hours = 6/24 = 0.25
12 hours = 12/24 = 0.5
etc

For simplicity make the dropdown values minutes (eg 10, 20, 30 etc)

So ... where D = value selected (in minutes) from dropdown ...
Code:
timeDelay = D/(60*24)
Hopefully the above provides what you need to write the userform code
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,683
Messages
5,445,941
Members
405,370
Latest member
Theglyde

This Week's Hot Topics

Top