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

chazrab · Jun 17, 2019

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

All above is 100% correct. Each time a preset msg appears, if I want, a Postpone button will send that same msg with any additions to the text to a future time of day, week or month when a future date and time is entered in d = DTPicker1.value(for the future date) and t = DTPicker1.value(for the time on the day of the future date date). The OK button closes that message as it becomes past history. A New Entry button accepts new values from DTPicker1, DTPicker2 and Textbox1 for another msg to display at a later time. The net result of all this is that multiple msgs, both new and postoponed can appear on the same or different days at different times. Hope this is yet a little clearer. Userform objecs = 1 Textbox1, 2 DTPickers(1 &2) and 3 buttons = OK, New Entry, Postpone. Thx, cr

Yongle · Jun 17, 2019

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

Ah .... that requires a re-think on my part - sorry I must have missed something earlier.

I was gong to dump all messages with same time to the same message box
- which will obviously not work if you want to be able to postpone individual messages
- that also explains why you were trying to drive everything from your message list

Objective reassessed
Every message pops up separately at its allotted time
When it pops up the user either
- accepts with OK
- OR postpones and enters a new date and time (and a new entry is added for that item whilst retaining the old item)
- the user can also create a new item

Something I am trying to figure out is what is best way to trigger the next message to pop up
- VBA can only do one thing at a time
- I will test a couple of ideas and return tomorrow

Yongle

chazrab · Jun 17, 2019

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

All reassessed objectives 100% accurate. RE "what is best way to trigger the next message to pop up" = not necessary to labor over. Whether a postponed or new entry userform message pops up, the OK button is always present on either form. You'd have to click OK on the Postponed userform msg, so it will vaporize, and then click the main calling button on the QAT to show a blank new userform for a new entry.

Once the new entry userform pops up, the OK button will also cause it to close and go away. The underlying data in the userforms, be they Postponed or New Entry, is still stored in rows and columns on the DATA sheet, or whatever the sheet is named. Net net - Postponed form pops up from postponed form source once up - OK closes it or POSTPONED button pushes it foorward. Blank New Entry form pops up from QAT button - the main calling source of the app. - i.e., MSGBOX.Show = a blank New Entry userform with only ONE OK button will appear.

When the date and time for that new form pops when it's scheduled to, TWO buttons now are now visible OK to close it and POSTPONE to move it to a future date and time with any new added text. The way this would work is of course on the New Entry form POSTPONE BUTTON.visible = False; New Entry Form now appears - POSTPONE BUTTON.visible = true. As said earlier, OK will always be on both, regardless. Perhaps more clearer. Many thx, once again for all your help. cr

Yongle · Jun 18, 2019

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

You have already developed this tool much further than I realised and I have been unnecssarily solving the "whole" picture rather than trying to provide you with the bits you need to fit into what you have already done. Apologies for that.
Let's try a different approach and look at the individual pieces that you need to dovetail into what you have already got working

Getting the correct values into Application.OnTime

The simplest way - convert combobox value to time value when it is written to a cell

When cell value is formatted as time then use that value directly

Code:

    Application.OnTime Range("B2"), "MyMacro"

If the cell value is TEXT then it must be converted
If it looks like 08:00 or 20:00 etc then use CDate function

Code:

     Application.OnTime [COLOR=#ff0000]CDate([/COLOR]Range("B4")[COLOR=#ff0000])[/COLOR], "MyMacro"

Any other style of text is likely to require you create your own code to do the converting
So if using values like 8am or 8pm ...

Code:

    Application.OnTime [COLOR=#ff0000]GetTime([/COLOR]Range("B6")[COLOR=#ff0000])[/COLOR], "MyMacro"

... which calls this function (requires modifying if minutes are also required)

Code:

Function GetTime(aTime As Variant) As Date
[COLOR=#006400][I]'remove am and pm[/I][/COLOR]
    If Right(aTime, 2) = "pm" Then
        aTime = Replace(aTime, "pm", "") + 12
    Else
        aTime = Replace(aTime, "am", "")
    End If
[I][COLOR=#006400]'build string[/COLOR][/I]
    aTime = aTime & ":00:00"
[I][COLOR=#006400]'convert to time[/COLOR][/I]
    GetTime = TimeValue(aTime)
End Function

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

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

1

[/td][td][/td][td]Time[/td][/tr]

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

2

[/td][td]Number formatted as time[/td][td]

08:00

[/td][/tr]

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

3

[/td][td]Number formatted as time[/td][td]

20:00

[/td][/tr]

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

4

[/td][td]String[/td][td]08:00[/td][/tr]

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

5

[/td][td]String[/td][td]20:00[/td][/tr]

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

6

[/td][td]String[/td][td]8am[/td][/tr]

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

7

[/td][td]String[/td][td]8pm[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Yongle · Jun 18, 2019

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

Please read post#14 before reading this

Has every message been dealt with?

Application.OnTime is reliant on the macro being active at the correct time but that will not always be the case

A method is required so that VBA can identify prior messages which have not been dealt with

Suggest extra column "D" to indicate message status
- in example below : "D" = dealt with , "X" = postponed
write "D" to worksheet when "OK" is clicked on userform
write "X" to worksheet when "Postpone" is clicked on userform

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

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

1

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

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

2

[/td][td]

11/06/2019

[/td][td]