RE: Displaying a Msgbox 1 hour after the workbook opens

[chazrab]

RE: Displaying a Msgbox 1 hour after the workbook opens

This code is in the Workbook Open() event:

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

 

[Yongle]

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

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

Sub [COLOR=#b22222]MyMacro[/COLOR]()
    MsgBox "Hello"
End Sub

 

[chazrab]

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

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

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]

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 ...

timeDelay = D/(60*24)

Hopefully the above provides what you need to write the userform code