Passing multiple times to Application.OnTime Method

[JeffGrant]

Hi All,

I am trying to loop the Applicaton.OnTime method through multiple values in a sheet.

The aim is to set up multiple Application.OnTime events.

The variable times are downloaded from a third party site and are being read in from cells so they can't be hard coded.

The OnTime's are firing off ok and with the right formats, however, only the last StartTime is being displayed in the msgbox because that is the last StartTime that has been created.

I assume that I should be using an array structure to create a StartTime variable and then pass that variable to the msgbox. But arrays are not my strong suit.

Much appreciated if someone can point me in the right syntax direction.

Cheers

Sub Time_Value_Message_Box()
Dim StartTime As Date

For ii = 1 To 3
    StartTime = Sheet10.Range("A" & ii).Value
    Application.OnTime StartTime, "Time_Value_MSG"
Next ii
End Sub

Sub Time_Value_MSG()
    MsgBox "Current Start Time is: " & StartTime
End Sub

 

[Jaafar Tribak]

I would pass the StartTime variable as an argument to the OnTime Method. I would even pass the caller cell... No need to use an array .

Sub Time_Value_Message_Box()
    Dim StartTime As Date, ii As Long, sAddrss As String
    For ii = 1& To 3&
        With Sheet10.Range("A" & ii)
            StartTime = .Value
            sAddrss = .Address(0&, 0&)
        End With
        Application.OnTime StartTime, "'Time_Value_MSG """ & sAddrss & """,""" & StartTime & "'"
    Next ii
End Sub

Sub Time_Value_MSG(ByVal CellAddress As String, ByVal StartTime As Date)
    MsgBox "Caller Cell is: [" & CellAddress & "]" & vbLf & _
           "Current Start Time is: " & Format(StartTime, "hh:mm:ss")
End Sub

 

[JeffGrant]

Perfect. Thank you Jaafar for your help.