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]

.
Look at the download.

- Changed the date / time entry

- Auto sorts all entries by date & time so they are always in the correct order

- When dismissing the current message, the messages to be displayed at a later time are auto-scheduled

- The display of Current Date & Time is for user info only. It is not required for accurate operation of the project and can be deleted if desire.

- Utilizes one form for entering data. Utilizes a second form for message display.

- The user needs to enter the date & time in this format : 6/18/2019 9:00 PM
. The column formatting will auto change it to 24 hr time.


Let me know if this satisfies your needs.


Workbook download : https://www.amazon.com/clouddrive/share/VO86Y4Yqr1mIIQGa7p7J7QE9FSd81dtwvrOVRdw8p5j

 

[Logit]

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

.
Updated download : https://www.amazon.com/clouddrive/share/gz4mTvOQ4ZU64ZFaenKVviaapZq2sUHyqm2y73DcIjF

 

[chazrab]

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

I entered a message for tomorrow 6/18/2019 at 5:30 AM. I'm anxious to see if it appears. If you went to the trouble to develop all this, many thx for all your help. If this app allows new messages to appear at future days and times, multiple times of the day, it satisfies my need 100%. I have just a few more questions - tomorrow. Thx again. Haven't seen that much effort even from MVP's in a long time. cr

 

[chazrab]

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

The message was on the screen waiting for a user interaction - I wanted to be able to customize the userforms a bit and add buttons, and move or copy over to my own excel app. When I tried to do that, I started having all kinds of issues. This error msg came up

Cannot run the macro ..://d:docs.live.net/ac484c1474897305 ALL FOLDERS/Documents/2019.xlsm! CMDFINDVAL'. The macro may not be available in this workbook or all macros may be disabled

I'm signed in to my O365 acct, and in a conversation with an O365 support csr, he told me that when you sign in to your O365 acct., the files are autmatically saved to OneDrive and to sign out. I did that and that same error message occurs. I can't fgiure out why this issue is happening. Just a few weeks ago I was able to test code and save it either on my laptop, or OneDrive. Even if the switch is turned off and the options are disabled, the error message still occurs. Please let me know if you have any ideas on this. I assumed it was OK to move your example which I downloaded into my vba project. Thx for any input on this and for all your help in developing this reminder app. cr

 

[Logit]

.
The code is yours to do with as you need to. Except make a profit off of it.

Not certain why the macro won't run in your environment. Note that there are macros in the regular module,
the userforms and in the Sheet 1 module.

Presumably, if you transferred all macros as written in the original locations, the code should run as expected. Admittedly,
I do not have experience using One Drive ... and I am assuming (we all know what happens when you assume) things are the
same in that environment.

If you've transferred all the code ... as written ... in the same locations as the original workbook .. and have not edited any of the
sheet names / macro names, etc. .. and it still doesn't run as expected, let me know. We will need to seek the assistance of someone
else familiar with One Drive.

 

[chazrab]

Hi Logit - solved all my OneDrive issues above - re downloaded updated version from #12 above, did not change any code or userform looks, did not move it into my app, saved to desktop, followed instructions exactly for date entry format, entered several new messages 2 minutes apart, clicked on OK button, repeated again 3-4 x, clicked on Close button - result - userfrom did not pop up at specified times. was careful to do everything as instructed. Don't know what's wrong - Thx for all your help. cr

 

[Logit]

.
I don't know what to advise as it works here as expected.

If you haven't changed anything in the download workbook ... the only thing that might be different is how the date and time
is being entered.

Both date and time need to be entered into Col A only.

Col B is not utilized and is there only as a spacer column between the Date / Time and the Message entered into Col C.

Date and Time should be entered as : 6/20/2019 10:50 AM

There is a single space between 2019 and 10:50.

There is a single space between 10:50 and AM.