Time Macro

[dgavin]

How do i change this so it only records the time in sheet 5 not sheet 1?





' Module level declaration of icount, inumberofcalls. This line
' must be at the top of the module sheet
Dim icount as Integer, inumberofcalls As Integer

Sub StartOnTime()
' Initialize icount to 1.
icount = 1
' Initialize inumberofcalls to 4.
inumberofcalls = 4
' Select the range of cells for formatting.
Range("A2:A" & inumberofcalls + 1).Select
' Format the selected cells as time.
Selection.NumberFormat = "h:mm:ss AM/PM"
' Start in cell A1.
Range("A1").Select
' Put the word "Time" in cell A1.
ActiveCell.Value = "Time"
' Start the OnTimeMacro.
Call OnTimeMacro
End Sub

Sub OnTimeMacro()
' Run the RunEvery5seconds macro inumberofcalls times.
If icount <= inumberofcalls Then
' Run the RunEvery5seconds macro in 5 seconds.
Application.OnTime Now + TimeValue("00:00:05"), _
"RunEvery5seconds"
' Increment icount by 1.
icount = icount + 1
Else
' Icount is greater than inumberofcalls, so exit the macro.
Exit Sub
End If
End Sub

Sub RunEvery5seconds()
' Places the current time in a cell.
ActiveCell.Offset(icount - 1, 0).Value = Format(Now(), _
"hh:mm:ss")
' Runs the OnTimeMacro again.
Call OnTimeMacro
End Sub

 

[shg]

Try this:

Sub StartOnTime()
    iCount = 1
    iNumberOfCalls = 4
    
    With Worksheets("Sheet5")
        .Range("A1").Value = "Time"
        .Range("A2").Resize(iNumberOfCalls).NumberFormat = "h:mm:ss AM/PM"
    End With
    
    OnTimeMacro
End Sub

 

[dgavin]

Thanks works great.

but i have other worsheets in the workbook

a whilst the macro runs in enters the time into whatever sheet i looking at.

I need to keep it in worksheet 5

 

[shg]

I'm really don't know what you're doing. Maybe this will do what you want:

Sub RunEvery5seconds()
    If ActiveSheet Is Worksheets("Sheet5") Then
        ActiveCell.Offset(icount - 1, 0).Value = Now()
    End If
    OnTimeMacro
End Sub