Variable input for Application.ontime

chazrab · Jun 13, 2019

This code is in a module and activates the timer

Code:

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

Code:

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

Code for ShowMsg

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
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 · Jun 13, 2019

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 · Jun 13, 2019

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

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

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 · Jun 14, 2019

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td]Date[/td][td]Time[/td][td]Message[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td]

11/06/2019

[/td][td]