Displaying a userform message at a future date and time read from values in a sheet

[chazrab]

Theses values are on a sheet named DATA in cols A = date, B = Time C = Message text:

6/17/2019	6:00 PM	1ST MSG
6/17/2019	7:00 PM	2ND MSG
6/17/2019	8:00 PM	3RD MSG

The values are saved and the pc is turned off at end of day on 6/16/2019.
The computer is turned on at 5 AM on 6/17/2019.
I'm confused about how to write simple but correct code that will display a userform
with Textbox1 to display 1ST, 2ND and 3RD messages at the correct time
of day on 6/17/2019.

I'm going in circles with figuring how to get this to work correctly.

x = Now() + TimeSerial(h, m, 0)
Application.OnTime t, "Runamsg"
Unload Me

My understanding is that the code has to be able to determine which messages are to be run at
which time on which day. I don't know if using Find and/or with a For Loop is necessary to do this correctly.
Can someone please give me a hand in working this out.

Thanks for anyone's help.
cr

 

[Logit]

Re: Displaying a userform message at a future date and time read from values in a sheet...

.
Paste in a regular module :

Option Explicit


Dim TimeToRun


Sub auto_open()
    Call ScheduleCopyPriceOver
End Sub


Sub ScheduleCopyPriceOver()
    TimeToRun = Now + TimeValue("00:00:01")
    Application.OnTime TimeToRun, "CopyPriceOver"
End Sub


Sub CopyPriceOver()
    Calculate
    Sheet1.Range("D2").Value = Now()
    Call ShowFormSched
    Call ScheduleCopyPriceOver
End Sub


Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub


Sub shwfrm()
    UserForm1.Show
End Sub


Sub ShowFormSched()
    If Sheet1.Range("A2").Value = Now() Then
        UserForm1.Show
    ElseIf Sheet1.Range("A3").Value = Now() Then
        UserForm1.Show
     ElseIf Sheet1.Range("A4").Value = Now() Then
        UserForm1.Show
    End If
End Sub

Create a UserForm1 / Show Non-Modal, paste this code in the form module :

Option Explicit


Private Sub CommandButton1_Click()
    Unload Me
End Sub


Private Sub UserForm_Initialize()
    If Sheet1.Range("A2").Value = Now() Then
        Me.TextBox1.Value = Sheet1.Range("C2").Value
    ElseIf Sheet1.Range("A3").Value = Now() Then
        Me.TextBox1.Value = Sheet1.Range("C3").Value
    ElseIf Sheet1.Range("A4").Value = Now() Then
        Me.TextBox1.Value = Sheet1.Range("C4").Value
    End If
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/TM7Jk32cHIgaP948U5ozvEz7a5AGLbj43zLcOjfZsQL

Note the formatting for date / time in the appropriate cells. Col D data is not required.

 

[chazrab]

Re: Displaying a userform message at a future date and time read from values in a sheet...

Hi Logit - thx for trying to help with this - I copied the code into a module and the code in the form module, created a userform with a button and textbox as instructed. I also downloaded what appears to be exactly the same thing from the Amazon link. Sheet1 has a button with the data in cols A, B, C and D. The userform pops up when I click BUTTON. I entered a value in the textbox. Nothing happens. Am I not following your instructions correctly, or am I just plain dumb and can't see what to do next?.

In the userform I've already created before I started asking from help from these posts, when a date from DTPicker1, a time from DTPicker2 and a message is entered into Textbox1, on my original userform, the data is stored on a sheet in cols A, B and C. The entries can be any date or time in the future. When the time for a message to display is = to now(), the message displays. Turning off the computer would not cause the app to lose its memories of dates and times, since they would be stored on a sheet and Find in conjunction with Application.onTime should cause the messages to appear on the correct days and times. I can't see any other way of getting this reminder to work correctly. If Find and/or Application.onTime is not necessary, please tell me if I'm not seeing something in what you sent to be able to do this, or my logic is not correct. Thx for helping to figure this out. cr

 

[Logit]

.
Your last post has created questions which leads me to believe you did not accurately describe the goal in your first post.

Do I now correctly understand you are wanting to use a USERFORM to enter the date and time and message to the sheet? Then, have the workbook
auto-display that message when the date and time is = to NOW() ?

From your first post it appeared you were manually entering the date / time / message onto the sheet directly ... and wanted the userform message to auto-display
at the appropriate date/time with the message in the text box on the form.

Regarding the download from my Amazon Cloud site .... you can disregard the command button on the sheet. That was used for testing purposes early in the building
of the workbook.

To test the workbook yourself .... enter today's date in each of the three cells (A2:A4) and enter a time that is ... say ... one minute into the future.
Example: the date would be 6/17/2019 ... then a space ... then say "1:00 pm". The 1 pm will auto change to 13:00:00 because of the formatting in the cell.
Each of the cells in Col A are formatted CUSTOM - m/d/yyyy h:mm:ss

The cells are formatted as such because the formula NOW() utilizes the same format. Also, the date and time were moved together in Col A to reduce the chance
of error when the user enters the date / time.

So ... if you set the cell time for say 1:00 pm ... and the next cell to 1:01 pm ... and the next cell to 1:02 pm .... save the workbook ... close and re-open ... when those times
are the current time ... the userform will auto-display. Of course you can use any time you want ... the suggested times are for demonstration purposes only.

 

[chazrab]

Hi Logit - this part of your reply above is 100% correct:
"Do I now correctly understand you are wanting to use a USERFORM to enter the date and time and message to the sheet? Then, have the workbook auto-display that message when the date and time is = to NOW() ?"

As mentioned and to be as brief as possible Date d =DTPicker1.value, Time t = DTPicker2.value, and Message = Textbox1.value are stored on Sheet1 entered from Userform1. The sheets rows increase with time as data is added to the first blank row in col A = date.
When the computer is turned on each day, the code searches down Sheet1 for a date match to d = Now(Date) in col A and a time match t =Now(Time). Userform1 is displayed with the message from col C for that Date and corresponding time. A button on the userform allows for Postponing that message to a later date and time with any changes in the message text(Textbox1). I hope this will clear things up. Sorry if I was too wordy and unclear in the original post. This is just a typical reminder found in many external apps - I want a custom version to use from within my Excel app. Thx again for all your help. cr

 

[Logit]

How many pending messages do you envision for any single day ?

 

[chazrab]

at most 10 -from 5 AM to 9 PM on any given day - interesting question. Why should that make a difference... -

 

[Logit]

.
Brainstorming ...

 

[Logit]

.
Once the message has been displayed .... can it be deleted from the sheet ?

 

[chazrab]

Yes