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

	A	B
1		Time
2	Number formatted as time	08:00
3	Number formatted as time	20:00
4	String	08:00
5	String	20:00
6	String	8am
7	String	8pm

Sheet: Sheet1

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

	A	B	C	D
1	Date	Time	Message	Status
2	11/06/2019	10:00	Check that project X is on schedule	D
3	12/06/2019	14:00	Staff weekly meeting	D
4	13/06/2019	16:00	WebMeeting with boss	D
5	14/06/2019	10:00	Pay Bill A	D
6	14/06/2019	10:00	Ring the school	D
7	14/06/2019	14:00	Book hotel for Wednesday	D
8	15/06/2019	14:00	Chase delivery of materials for project X	D
9	15/06/2019	14:00	Ring Peter re Team Meeting	D
10	16/06/2019	10:00	Pay Joiners Inc
11	16/06/2019	14:00	Team meeting tomorrow
12	18/06/2019	10:00	Feed the cat	D
13	18/06/2019	14:00	Phone bank re cheque	X
14	18/06/2019	14:00	Phone bank re overdraft	X
15	18/06/2019	20:00	Arrange transpost for delivery X
16	18/06/2019	20:00	Feed the fish
17	18/06/2019	20:00	Phone bank re oversight
18	18/06/2019	20:00	Phone home

Sheet: DATA

Yongle · Jun 18, 2019

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

Please read post#14 onwards before reading this

Sorting messages

The UserForm adds items with various dates and times to sheet "DATA"
Those items are added in the order created
Below is a simple procedure that can be called to sort the data in the way it is likely to be required by VBA

Code:

Sub SortMessages()
    With Sheets("Data")
        .Sort.SortFields.Clear
        With .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(,[COLOR=#ff0000] 4[/COLOR])
            .Sort .Range("B2"), xlAscending
            .Sort .Range("A2"), xlAscending
        End With
        .Sort.SortFields.Clear
     End With
End Sub

Resize(, 4) allows for column D (as suggested in post#15)

Yongle · Jun 18, 2019

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

Please read post#14 onwards before reading this

Which message is next?

Here is the simplest way I can think of to return the correct "next" message each time

With column D = message status & messages soted by date and time (as explained in posts #15 & #16 )
.. the FIRST message with a blank cell in column D
.... is the correct "next" message
...... ( = earliest message which has not been dealt with)

The code below
- calls itself recursively
- column B = TimeValues(not text)
- column A = DateValues (not text)
- message returned to a message box (you need to return it to the textbox in your userform)

Code:

Sub NextMessageToDisplay()
    Dim cel As Range

[I][COLOR=#006400]'get first blank cell in column D[/COLOR][/I]
    Set cel = Sheets("DATA").Range("D1").End(xlDown).Offset(1, -3)
    
[I][COLOR=#006400]'if message date and time is before current date and time then ...[/COLOR][/I]
    If cel + cel.Offset(, 1) <= Now Then
[COLOR=#006400][I]'display message[/I][/COLOR]
        MsgBox cel.Offset(, 2)
       Call NextMessageToDisplay
    Else
[I][COLOR=#006400]'display next message at appropriate time[/COLOR][/I]
        Application.OnTime cel.Offset(, 1), "NextMessageToDisplay"
    End If
End Sub

With data sorted as below, the next message displayed is

"Pay Joiners Inc"

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	Date	Time	Message	Status
2	11/06/2019	10:00	Check that project X is on schedule	D
3	12/06/2019	14:00	Staff weekly meeting	D
4	13/06/2019	16:00	WebMeeting with boss	D
5	14/06/2019	10:00	Pay Bill A	D
6	14/06/2019	10:00	Ring the school	D
7	14/06/2019	14:00	Book hotel for Wednesday	D
8	15/06/2019	14:00	Chase delivery of materials for project X	D
9	15/06/2019	14:00	Ring Peter re Team Meeting	D
10	16/06/2019	10:00	Pay Joiners Inc		<---Finds this empty cell
11	16/06/2019	14:00	Team meeting tomorrow
12	18/06/2019	10:00	Feed the cat	D
13	18/06/2019	14:00	Phone bank re cheque	X
14	18/06/2019	14:00	Phone bank re overdraft	X
15	18/06/2019	20:00	Arrange transpost for delivery X
16	18/06/2019	20:00	Feed the fish
17	18/06/2019	20:00	Phone bank re oversight
18	18/06/2019	20:00	Phone home

Sheet: DATA

Yongle · Jun 18, 2019

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

please read this AFTER reading posts #14 to #17

Final contribution for today - some of this has now become "pseudo-code simply there as a bit of a guide

Calling the userform to postpone a message

1.Userform code requires access to "cel" variable

So delete this line in NextMessageToDisplay

Code:

  Dim cel As Range

and create a public variable at the top of the module ABOVE all procedures (makes the variable available to other modules including userform module)

Code:

  Public cel As Range

2. Replace this line in NextMessageToDisplay

Code:

        MsgBox cel.Offset(, 2)

.. with something like this (userform name needs amending)

Code:

        If MsgBox(cel.Offset(, 2) & vbCr & vbCr & "Postpone above message?", vbYesNo, "Postpone??") = vbYes Then
            Userform1.Show
            Exit Sub
        Else
[I][COLOR=#006400]'update message status to "Dealt with"[/COLOR][/I]
            cel.Offset(, 3) = "D"
        End If

3. Postpone procedure requires something similar to this

Code:

[I][COLOR=#006400]'message text etc[/COLOR][/I]
  TextBox1.Value = Cel.Offset( , 2) - likewise for other values being transferred into userform objects
[I][COLOR=#006400]
'prevent past date being seleced otherwise message visibility lost[/COLOR][/I]
  ?? perhaps date picker already does this, otherwise insert code to control input

[I][COLOR=#006400]'update message status ONLY if date or time has changed otherwise message visibility lost[/COLOR][/I]
   If cel.offset( ,1) <> TimeValue of ComboBox1 Or cel <> Date Value of UserformDateObject Then cel.Offset(, 3) = "P"

[I][COLOR=#006400]'trigger next message[/COLOR][/I]
   Call NextMessageToDisplay

chazrab · Jun 18, 2019

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

Hi Yongle - you really have put a lot of effort into this and I appreciate it very much - I need to study this - a recent development has taken my focus off this but affects it a great deal -apparently the developers of O365 have seen fit to force a user to have changes to worksheets and code automatically saved to OneDrive. This has created a problem when trying to run and test this code out - I keep getting

Code:

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

this error message when the code searces for a future date. I signed out of my account but the same thing happens. As soon as I can resolve this, I want to continue full force with finishing the development of this reminder app. My vb editor window also got all messed up and I can't figure out how to keep the Project Explorer and code windows to stay locked in the docked positions next to each other in the same position all the time as it was before. The windows don't stack anymore - they appear as separate windows on top of windows and I have to rearrange the editor window. I don't know if I accidently clicked a button on the toolbar or what I did but it's very annoying. Sorry for the venting - I just need to get on with this - thx again for all your help , time and input...cr

Yongle · Jun 18, 2019

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

Consider starting a new thread to help resolve OneDrive issue - others have probably suffered similarly

Good luck

Variable input for Application.ontime

chazrab

Well-known Member

chazrab

Well-known Member

Excel Facts

Yongle

Well-known Member

chazrab

Well-known Member

Yongle

Well-known Member

Yongle

Well-known Member

Yongle

Well-known Member

Yongle

Well-known Member

Yongle

Well-known Member

chazrab

Well-known Member

Yongle

Well-known Member

Similar threads

Forum statistics

Share this page

Variable input for Application.ontime

Well-known Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock