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

AR() is just an array that is loaded up with your data. If you don't explicitly say what sheet you want, the range function will default to the active sheet. I've ammended the code below to look at the sheet named 'DATA'. Seems to be working.

Sub LOADTIMES()
Dim ws As Worksheet: Set ws = Sheets("DATA")
Dim ar() As Variant: ar = ws.Range("A2:C" & ws.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)
If UserForm1.Visible = False Then
    UserForm1.TextBox1.Value = txt
    UserForm1.Show
End If
End Sub

 

[chazrab]

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

AR() is just an array that is loaded up with your data. If you don't explicitly say what sheet you want, the range function will default to the active sheet. I've ammended the code below to look at the sheet named 'DATA'. Seems to be working.

Sub LOADTIMES()
Dim ws As Worksheet: Set ws = Sheets("DATA")
Dim ar() As Variant: ar = ws.Range("A2:C" & ws.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)
If UserForm1.Visible = False Then
    UserForm1.TextBox1.Value = txt
    UserForm1.Show
End If
End Sub

Hi ikrobbo314 - well, I feel encouraged. I tested your code with 5 entries 3- minutes apart all on today's date at different times this afternoon. They all displayed with all of the correct messages. For me, the real test is when I open the workbook tomorrow morning. The code is placed in the Workbook Open event and the macro in a standard module. If this code displays the correct messages on the rows and cols in the DATA sheet at the given times tomorrow in col B, this will be a major accomplishment in my view - a customized reminder for Excel within Excel. I've added additional functionality to the userform - an expanding textbox for displaying long messages with a click. Will keep you posted on tomorrow's results - I don't expect any problems though - to me, if your code read today's dates and times with all the corresponding messages correctly, and it did as mentioned, it should display any message for any future date and time when the workbook is saved, the computer shut down, turned on each day, and the Excel app is opened. Thanks again for all your help in this. cr

 

[lrobbo314]

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

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

 

[chazrab]

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

There's a big weird graphic in your reply - no text comments - was that intentional ?