Variable input for Application.ontime

[chazrab]

This code is in a module and activates the timer

Sub runShowMsg()
'called from when workbook open from call runShowMsg line of code
Dim RunTimer As Date
RunTimer = Now() + TimeValue("00:00:10") 
Application.OnTime RunTimer, "ShowMsg"
End Sub

This code is in a button on the MSGINPUT userform

Private Sub CMDPOSTPONE_Click()
RunTimer = Now() + TimeValue("00:00:10")
Application.OnTime RunTimer, "ShowMsg"
Unload Me
End Sub

Code for ShowMsg

Sub ShowMsg()
Dim s As String
Dim da As Date
Dim t As String
da = Date
Dim rgFound As Range
On Error Resume Next
Set rgFound = Range("DATES").FIND(What:=da, LookIn:=xlValues)
If rgFound Then
  t = rgFound.Offset(0, 1).Value
  s = rgFound.Offset(0, 2).Value
  MSG.TextBox1.Value = s
  MSG.Show
Else
MSG.TextBox1.Value = "No opening messages today"
End If
End Sub

and just has ShowMsg appear after 10 seconds as formatted
in TimeValue("00:00:10"). Simple enough because 10 seconds is
hard coded by specifying that value in the code.

I want to be able to run ShowMsg after ANY time that I specify
using a variable for Timevalue.

There are 3 pieces of information on MSGINPUT userform within the application
that puts new data for the MsgBox in the first blank row of Sheets("DATA")
col A = 6/13/2019 or ANY date from a DTPicker
col B = a number which is the time later in the day I want ShowMsg to run
col C = any new msg I type into Textbox1 on MSGINPUT
The important thing here is the value in col B. Combobox1 has
a list of values that represent different times of the day to copy to col B.

The problem I'm having is, if I don't format these values correctly in code, they're ignored
in the Runtimer code. IT would have to be RunTimer = Now() + the correctly formatted value input from Combobox1
cell B of the first blank row on Sheets("DATA"), and not hardcoded in TimeValue("00:00:00") each time I want to change a time to run. I know this is long, but I could not make it shorter without sacrificing a clear understanding of what I'd like to accomplish.

Thanks very much for anyone's help on this.
cr

 

[Yongle]

Re: Asking for help on a variable input for Application.ontime...

Sorry but I am confused by your explanation
Why are you copying anything anywhere? Why not select a time in the combobox and simply use the selected value?

Is command button on userform what should set ShowMsg running?

 

[chazrab]

Re: Asking for help on a variable input for Application.ontime...

Thanks for getting back. Sorry - it was not my intention to be confusing. I can see how all this code and my explanation can be easily misinterpreted. As simple as possible:
:
I. I created a userform named MSGINPUT within the same application. I show that form from a button on the QAT. The form has 1 Textbox1, s for the input message, 1 d1 = DTPicker1.value to enter the date to display the message and a combobox to select a time t = combobox1.value on the date selected from DTPicker.
2. These values are copied to the first blank row in a sheet named DATA in cols A = Date = d, B = Time, t and C = message text, s.
3. So what I now have is a sheet with data in three cols with increasing rows of data over time.
4. If I fill out the userform for a message to appear tomorrow at 10 AM that says “pay DIRECTV bill today”, the first empty row in DATA would have
Col A = 6/14/2019
Col B = 10 AM
Col C = “Pay DIRECTV bill today”
4. When the workbook is opened, the words call runShowMsg
Runs this code

Sub runShowMsg()
'called from when workbook open from call runShoMsg line of code
Dim RunTimer As Date
RunTimer = Now() + TimeValue("00:00:10") '/ (60 * 24)  'TimeValue("00:00:10")
Application.OnTime RunTimer, "ShowMsg"
End sub

And
ShowMsg, in the same module, runs this code

Sub ShowMsg()
Dim s As String
Dim da As Date
Dim t As String
da = Date
Dim rgFound As Range
On Error Resume Next
Set rgFound = Range("DATES").FIND(What:=da, LookIn:=xlValues)
If rgFound Then
  t = rgFound.Offset(0, 1).Value
  s = rgFound.Offset(0, 2).Value
  MSG.TextBox1.Value = s
  MSG.Show
Else
MSG.TextBox1.Value = "No opening messages today"
End If

What ShowMsg does is use FIND to locat the Date in col A and use this value and the offset values of t and s to run and display MSG on the correct day, 6/14/2019 from col A, with the correct time, t from col B and finally the text s in col C. I don’t know of any other way to schedule MSG(this is just a custom userform) on a future day other than using FIND in a column of data to locate the correct date and corresponding values on the same row to display MSG on the right day, at the right time, with the right message. I hope this is a little clearer. If there’s a shorter, more elegant but no less effective way to do this, please let me know. I wanted to show no code, but I felt it best to show for explanation purposes.

Thx again for all your help and effort. I can see that you are trying to help me with this, can see and understand some things I can’t at this point.
cr

 

[Yongle]

Re: Asking for help on a variable input for Application.ontime...

OK I think I now understand what you are trying to do - lets make sure

With this table of requirements (ignoring UK date formatting )
Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	Date	Time	Message
2​	11/06/2019​	10:00​	Check that project X is on schedule
3​	12/06/2019​	14:00​	Staff weekly meeting
4​	13/06/2019​	14:00​	WebMeeting with boss
5​	14/06/2019​	10:00​	Pay Bill A
6​	14/06/2019​	10:00​	Ring the school
7​	14/06/2019​	14:00​	Did you remember to book hotel for Wednesday?
8​	15/06/2019​	14:00​	Chase delivery of materials for project X
9​	15/06/2019​	14:00​	Ring Peter re Team Meeting
10​	16/06/2019​	10:00​	Pay Joiners Inc
11​	16/06/2019​	14:00​	Team meeting tomorrow
12​	17/06/2019​	10:00​	Feed the cat
13​	17/06/2019​	14:00​	Phone bank re overdraft

Sheet: Sheet1

Then this is what needs to be delivered via messages today

14/06/2019 10:00:00 Pay Bill A
14/06/2019 10:00:00 Ring the school
14/06/2019 14:00:00 Did you remember to book hotel for Wednesday?

But perhaps you switch PC on at 10.30 today ...
- should 10am messages be delivered to screen immediately?

 

[chazrab]

Re: Asking for help on a variable input for Application.ontime...

OK I think I now understand what you are trying to do - lets make sure

With this table of requirements (ignoring UK date formatting )
Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	Date	Time	Message
2​	11/06/2019​	10:00​	Check that project X is on schedule
3​	12/06/2019​	14:00​	Staff weekly meeting
4​	13/06/2019​	14:00​	WebMeeting with boss
5​	14/06/2019​	10:00​	Pay Bill A
6​	14/06/2019​	10:00​	Ring the school
7​	14/06/2019​	14:00​	Did you remember to book hotel for Wednesday?
8​	15/06/2019​	14:00​	Chase delivery of materials for project X
9​	15/06/2019​	14:00​	Ring Peter re Team Meeting
10​	16/06/2019​	10:00​	Pay Joiners Inc
11​	16/06/2019​	14:00​	Team meeting tomorrow
12​	17/06/2019​	10:00​	Feed the cat
13​	17/06/2019​	14:00​	Phone bank re overdraft

Sheet: Sheet1

Then this is what needs to be delivered via messages today

14/06/2019 10:00:00 Pay Bill A
14/06/2019 10:00:00 Ring the school
14/06/2019 14:00:00 Did you remember to book hotel for Wednesday?

But perhaps you switch PC on at 10.30 today ...
- should 10am messages be delivered to screen immediately?

If I understand your last question correctly, if the PC is turned on at a time PAST the date in col B, the time value in col B would be ignored, would it not ? 99.999 % of the time I turn on my, in this case laptop, around 5 AM 24 x 7. So any times in col B will always be in the future, whether on that day or a future day. Beyond that, the lingering question for me still has to do with how you would have t to be formatted to be read correctly to run the code in this code block:

Sub runShowMsg()
'called from when workbook open from call runShoMsg line of code
Dim RunTimer As Date
RunTimer = Now() + TimeValue("00:00:10") '/ (60 * 24)  'TimeValue("00:00:10")
Application.OnTime RunTimer, "ShowMsg"
End sub

t has to be formatted in such a way for RunTimer to recognize the value of t - instead of RunTimer = Now() + Timevalue("00:00:00"), it
would be RunTimer = RunTimer = Now() + t, where t is the correctly formatted value for Time in col B, otherwise a runtime error will be generated. That's where I'm stuck here. Whar I have now, while trying to figure all this out is RunTimer = Now() + Timevalue("00:00:10") - all that does is have MSG display 10 seconds after Now() when the laptop is turned on. That 10 seconds can be any correct value. Thx again for all your help in trying to work on this. cr

 

[Yongle]

Re: Asking for help on a variable input for Application.ontime...

Bear with me
- I think you need to get something else right BEFORE worrying about the format of the data (which is probably irrelevant - see below)
- the macro requires triggering several times each day not once

If you have already figured out how to do this let me know.

This is my outline thinking
Let's say there are 6 possible times
- 8am, 10am,12noon,2pm,4pm,6pm
- macro needs to be triggered 6 times during the day
- at each trigger loop through the list of potential messages and dump those required to screen
- Time Values are held in the macro (not the celll)
- the format in the cell is not relevant as the macro will only be using the value to group same times together

If this is not helpful, let me know

thanks

 

[chazrab]

Re: Asking for help on a variable input for Application.ontime...

If I understand your last question correctly, if the PC is turned on at a time PAST the date in col B, the time value in col B would be ignored, would it not ? 99.999 % of the time I turn on my, in this case laptop, around 5 AM 24 x 7. So any times in col B will always be in the future, whether on that day or a future day. Beyond that, the lingering question for me still has to do with how you would have t to be formatted to be read correctly to run the code in this code block:

Sub runShowMsg()
'called from when workbook open from call runShoMsg line of code
Dim RunTimer As Date
RunTimer = Now() + TimeValue("00:00:10") '/ (60 * 24)  'TimeValue("00:00:10")
Application.OnTime RunTimer, "ShowMsg"
End sub

t has to be formatted in such a way for RunTimer to recognize the value of t - instead of RunTimer = Now() + Timevalue("00:00:00"), it
would be RunTimer = RunTimer = Now() + t, where t is the correctly formatted value for Time in col B, otherwise a runtime error will be generated. That's where I'm stuck here. Whar I have now, while trying to figure all this out is RunTimer = Now() + Timevalue("00:00:10") - all that does is have MSG display 10 seconds after Now() when the laptop is turned on. That 10 seconds can be any correct value. Thx again for all your help in trying to work on this. cr

Correct - I need to have different messages appear at different times. I am trying to create what this app I currently use for daily and future reminders does with a custom userform, DTPicker and a textbox or two - http://www.cutereminder.com/ The standard version is free. This reminder has a date and time fields which are tied to the computer's system clock. And I don't know how to do anything inside or outside of vba that enables use of the system clock. It's great for reminder msgs and not obtrusive at all - I just want a more customized version that integrates into my excel app. Thx 3rd or 4th time for all your help. By your comments, you can see things I can't regarding this solution right now. cr

 

[Yongle]

Re: Asking for help on a variable input for Application.ontime...

ok - I will post a suggested approach for you later today

 

[chazrab]

Re: Asking for help on a variable input for Application.ontime...

Thx Yongle = the little Cute Reminder app is EXACTLY what I want to create in excel with what I see are just three objects - Textbox1 for messages, DTPicker1 for a date entry from the drop down calendar, i.3., 6/17/2019 = now() or today(), and DTPicker2 formatted as time - i.e., 1:40 PM. for the userform to display at that time. This is what I see was used to develop the new reminder popup. The button with three dots at the bottom triangle on the sidebar displays all of the future stored dates and times of future messages. Being able to pull up a history table of past reminder messages is not necessary for me, only future messages. Thx again. I hope this explains what I need the code to be able to do correctly. cr

 

[Yongle]

Re: Asking for help on a variable input for Application.ontime...

No problem

Thanks for the link but l am unable download any "test" software onto my PC

You have already explained that what you want earlier but, to clarify (so that we get off to a good start) ....

- PC is switched on at 9am (example time)

- workbook is opened, WorkBook_Open triggers the macro to run and return a message box
- MESSAGE BOX = messages which should have already been actioned before 9am today (if any)
- clicking OK triggers macro to run at next preset time
- at preset time MESSAGE BOX = messages due at that time
- clicking OK triggers macro to run at next preset time
- .... until last scheduled time of day

If anything above is not what your are expecting then let me know

I will endeavour to make the code simple and clear and will post it later today as promised (probably around 9pm when I get home)