Getting Application.ontime in a For loop to work 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("DATAEND").Range.Cells(i, 4).Value
 DATETIME.Show
End Sub

When I run this I get a “Subscript out of range at this line in the RunNewMSG macro:

DATETIME.TextBox1.Value = Sheets("DATAEND").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 subscript is out of range ? What’s wrong with this ? Thx for any help. cr

 

[NdNoviceHlp]

You have an "i" instead of a 1 in the macro. Running a ontime code within a loop? It seems like this is not going to work the way U want ie. the loop may run hundreds/thousands of times within a milisecond and you're calling the macro hundreds/thousands of times also within a milisecond. HTH. Dave

 

[chazrab]

Thanks for the reply and trying to help - I made a mistake in typing the original code - it should be an "i", not the number "1". The i tis the value in col A - in this case, a valid date in the format 6/29/2019. Addressing the 2nd point you brought up, this won't be an infinite loop, as you say it iwll, will it ? This line of code would terminate the looping process at the last entry. Any blank rows below that last entry would stop the For Loop by this line

For i = 2 To [A65536].End(xlUp).Row

By all means, let me know if I'm incorrect - if you can think of a better way to schedule a userform reminder to pop up at different times on different days in the future, display a message on that day at that time, please let me know. The only way I can see to do this is by placing the entries in rows A - Date B - Time C - message to display on date A and t=Time B. This of course would be placed in the Workbook Open event. The code would have to loop say every 30 minutes(or a time I determine) to check for values to display. I don't know of any other way to develop a custom excel reminder. Please let me know if you have a better way. Thx for all your help. cr

 

[NdNoviceHlp]

In the macro RunNewSetMSG you haven't declared the "i" so it has no value and therefore errors. Not an infinite loop as it seems on reread that U have dates for msgs in "A" and I missed the conditional date match... U should "Exit For" after the condition is met and save some time. Trial making the "i" a Public variable in a module... which should fix your error. HTH. Dave