Getting Application.onTime to work in a For Loop correctly

[chazrab]

This code

Private Sub cmdFindDate_Click()
Dim i, n As Integer
Dim t
  For i = 2 To [A65536].End(xlUp).Row
    n = Day(Cells(i, 1).Value)
       If n = Day(Now()) Then
          t = Now() + TimeSerial(0, 0, 10)
          Application.OnTime t, "RunNewSetMSG"
       Else
          MsgBox "value does not match"
       End If
   Next
   Exit Sub
End Sub

The code for the macro RunNewsetMSG is

Sub RunNewSetMSG()
 DATETIME.TextBox1.Value = Sheets("DATAENT").Range.Cells(i, 4).Value
 DATETIME.Show
End Sub

When I run this I get a “Wrong number of arguments or invalid property assignment" at this line in the RunNewMSG macro:

DATETIME.TextBox1.Value = Sheets("DATAENT").Range.Cells(1, 4).Value

What I want is to display the DATETIME userform with each UNIQUE VALUE in textbox1 read from the For i = loop every 10 seconds. What does "Wrong number of arguments or invalid property assignment" mean ? What's wrong with this ? Can anyone please hlep resolve this. Thx cr

 

[lrobbo314]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

This

DATETIME.TextBox1.Value = Sheets("DATAENT").Range.Cells(1, 4).Value

should be

DATETIME.TextBox1.Value = Sheets("DATAENT").Cells(1, 4).Value

 

[chazrab]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

This

DATETIME.TextBox1.Value = Sheets("DATAENT").Range.Cells(1, 4).Value

should be

DATETIME.TextBox1.Value = Sheets("DATAENT").Cells(1, 4).Value

Thanks for the prompt reply irobbo314 - I tried that at the line in question which is

DATETIME.TextBox1.Value = Sheets("DATAENT").Cells(i, 4).Value

and this time I get a Run time error 1004. "Application defined or object defined error". I do want to mention that I typed too fast in the original post above and made a mistake. What is cells(1,4) should be cells(i,4) - that is the value of i in the For Loop, not the number 1. The code generates the run time error 1004 "Application defined or object defined error" with the 1 changed to i. If this is to work correctly, DATETIME userform should pop up every 10 seconds with the value of cells(i,4) displayed in Textbox1. Seems simple enough, just can't figure out why this doesn't work. Thx again for all your help. cr

 

[lrobbo314]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

Try

Private Sub cmdFindDate_Click()
Dim i, n As Integer
Dim t
  For i = 2 To [A65536].End(xlUp).Row
    n = Day(Cells(i, 1).Value)
       If n = Day(Now()) Then
          t = Now() + TimeSerial(0, 0, 10)
          [COLOR=#0000ff]Application.OnTime t, "'RunNewSetMSG " & i & "'"[/COLOR]
       Else
          MsgBox "value does not match"
       End If
   Next
   Exit Sub
End Sub

Also, you probably need the userform to be shown before setting the value to the textbox.

Sub RunNewSetMSG()
 DATETIME.Show
 DATETIME.TextBox1.Value = Sheets("DATAENT").Range.Cells(i, 4).Value 
End Sub

 

[chazrab]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

Try

Private Sub cmdFindDate_Click()
Dim i, n As Integer
Dim t
  For i = 2 To [A65536].End(xlUp).Row
    n = Day(Cells(i, 1).Value)
       If n = Day(Now()) Then
          t = Now() + TimeSerial(0, 0, 10)
          [COLOR=#0000ff]Application.OnTime t, "'RunNewSetMSG " & i & "'"[/COLOR]
       Else
          MsgBox "value does not match"
       End If
   Next
   Exit Sub
End Sub

Also, you probably need the userform to be shown before setting the value to the textbox.

Sub RunNewSetMSG()
 DATETIME.Show
 DATETIME.TextBox1.Value = Sheets("DATAENT").Range.Cells(i, 4).Value 
End Sub

I made all the changes you wrote in. This time with your code changes this message appears and halts the excution:

 "Cannot run the macro "https//d.docs.live.net/ac484e1474897305/2019.xlsm.: "RunNewSetMSG 4",  The macro may not be available in this workbook or all macros may be disabled".

This macro is in a standard module. Another thing puzzling me is this long address implying that's where the macro is stored. Also, what's that 4 doing attached at the end of the macro name. I did not put that in there. Is the For Loop not stopping at the 3rd and last entry in the sheet.(The data is only 3 rows for simplicity and testing) Hopefully this is revealing enough to correct the problem. Thx again for all your help. cr

 

[lrobbo314]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

Sub RunNewSetMSG([COLOR=#0000ff]i as Integer[/COLOR])
 DATETIME.Show
 DATETIME.TextBox1.Value = Sheets("DATAENT").Range.Cells(i, 4).Value 
End Sub

 

[lrobbo314]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

I don't think this is going to work anyway. Your for loop will call the application.ontime function in rapid succession. So n number of ontime events will fire one after the other 10 minutes after you click the button. And then you will get errors because it will be trying to open the form while it's already open. At least that's what's been happening on my test workbook trying to replicate your situation. You might need to rethink your approach for whatever it is that you are trying to accomplish.

 

[chazrab]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

I'm beginning to agree with you. The reason I chose to embed an Application.onTime event within a For Loop was to be able to do this:
1. a userform enters a date in col A, a time in col B and a message in col C on row 1. A second set of data is entered on the second row and so on.
2. Code in the Workbook Open event checks if the date in col A = Now(), and if it is, then checks the value in col B for the time to display the message in col C in Textbox1 of the userform. So, if I entered data yesterday that put 6/29/2019 in col A, 7:10 PM in col B and "watch Astros" in col C, the userform would display at 7:10 with the message on that day. This is just a reminder software app that is created in and for Excel. CuteReminder.com has a free app which I have that does all that but it's user interface is boring. That said, the app probably uses the WIN API and the system clock, since it is not required to run while Excel is open. The development of this and other Reminder apps probably use C+ or C++ or some other fancy language that I'm not familiar with. I'm comfortable with the idea of trying to emulate this reminder app in Excel and will use it only while it is open, as I am working in Excel from 5 AM - 9 PM M-F.

So that's it. I just want to be able to create a reminder app within the Excel environment using VBA. I think that has been done before, but I cannot find any code that does this successfully. A For Loop would(should) terminate execution when it encounters the first blank row in the data. Applicaltion.onTime is the only way I know to schedule a userform to pop up at a future date and time and not have to resort to a set timer code block as this seems like it would run continually in the background using up memory constantly. Tried to make this as short as possible but I couldn't - at least now I think you may have a better understand of what I want to create - a simple reminder app within Excel. Thanks again for all your help. Sorry for the long dissertation. cr

 

[lrobbo314]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

How about this. Based off of your example I have in A1:C4

Date	Time	Comment
6/28/2019	1:00 PM	One
6/29/2019	4:31 PM	Two
6/29/2019	4:32 PM	Three

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Then I have this code that runs through each record. If the cell in column A is today, then it sets the ontime for the value in column B, and passes the comment from column C to be displayed in a messagebox. Then you can just reference the LOADTIMES sub into the workbook_open event.

Sub LOADTIMES()
Dim AR() As Variant: AR = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row).Value

For i = LBound(AR) To UBound(AR)
    If AR(i, 1) = DateValue(Now) Then
        Application.OnTime AR(i, 2), "'RUNMSG """ & AR(i, 3) & """'"
    End If
Next i
End Sub

Sub RUNMSG(txt As String)
MsgBox txt
End Sub

 

[chazrab]

Re: Getting Appiication.onTime to work in a For Loop correctly...edited

This works great - that is, it displays Two and Three at times I put in in minutes to test in a MsgBox, not in the Textbox1.value of a custom userform.

Your code is a little beyond my understanding so - how does the code know which sheet the data is on? Is AR() a reference to any sheet ? I want to be able to incorporate this into my app. The Sheet name the Date Time and Comment is on is named DATA. I also want to use a user form instead of the standard MsgBox. Userform1.Textbox1.Value = the correct reference to the value of Comments for that date and time. Will this code

 Application.OnTime AR(i, 2), "'RUNMSG """ & AR(i, 3) & """'"

generate the same errors we were having when trying to set the correct value of Comments in the userform in RUNMSG if this line is
substituted for Msgbox txt

Sub RUNMSG(txt As String)
    Userform.Textbox1.Value(or.Text) =  the correct text value of Comments at that Date and Time row     'MsgBox txt
End Sub

Thanks again for helping me to make this work. Relatively simple code with few lines - I just don't understand all of the references 100%
cr