Take a look at Chip Pearson's notes on OnTime at:
http://www.cpearson.com/excel/ontime.htm

Pay particular attention to the "Stopping A Timer Process" section.

In his example, he uses a public variable named RunWhen to keep track of that
exact time (which is needed to stop the routine).

And I think I'd add a boolean variable to the "start Log" button routine that
checked to see if it's already scheduled. Just keep track if they turned it on
and if they did, exit without doing another schedule.

With minor modifications to Chip's code:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "The_Sub"
Public TimerIsArmed As Boolean
Sub StartTimer()
If TimerIsArmed Then Exit Sub
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedu=cRunWhat, _
schedule:=True
TimerIsArmed = True
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedu=cRunWhat, schedule:=False
TimerIsArmed = False
End Sub
Sub The_Sub()
MsgBox "hi from the sub"
TimerIsArmed = False
StartTimer
End Sub

"dale1627 <" wrote:

Hoping that some one here can help me with a data logging spreadsheet
that I am trying to set up. Right now I am gathering data from one of
our machines that has a Programmable Logic Controller with a network
card in it. I am getting my data, but am having a hard time with my
logging and clear log. What happens is, when I press the button "Start
Log" my data should be saved in 15 min increments. When I am finished
logging, I want to clear the log and stop it until I am ready to begin
agian. I have my macros set up and am currently logging, problem is
when I press my "Clear Log" button everything goes crazy. It may log
every few seconds or every minute. Also if someone presses the "Start
Log" button more that once this occurs. Hopefully some one will be
willing to look at my macro and set me straight.

"Start Logging" macro-

Sub LoggingFunction()

Dim nextCell As Integer
If ThisWorkbook.Sheets(3).Cells(1, 1) = "" Then
nextCell = 5
Else
nextCell = ThisWorkbook.Sheets(3).Cells(1, 1)
End If

ThisWorkbook.Sheets(2).Cells(nextCell, 1) = Now()
ThisWorkbook.Sheets(2).Cells(nextCell, 2) =
ThisWorkbook.Sheets(1).Cells(3, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 3) =
ThisWorkbook.Sheets(1).Cells(4, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 4) =
ThisWorkbook.Sheets(1).Cells(5, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 5) =
ThisWorkbook.Sheets(1).Cells(6, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 6) =
ThisWorkbook.Sheets(1).Cells(7, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 7) =
ThisWorkbook.Sheets(1).Cells(8, 2)
ThisWorkbook.Sheets(2).Cells(nextCell, 8) =
ThisWorkbook.Sheets(1).Cells(9, 2)

nextCell = nextCell + 1
ThisWorkbook.Sheets(3).Cells(1, 1) = nextCell

Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction"
Exit Sub

End Sub

"Clear Log" macro-

Sub ClearLog()

Dim i As Integer

i = 5
For i = 5 To ThisWorkbook.Sheets(3).Cells(1, 1)

ThisWorkbook.Sheets(2).Cells(i, 1) = ""
ThisWorkbook.Sheets(2).Cells(i, 2) = ""
ThisWorkbook.Sheets(2).Cells(i, 3) = ""
ThisWorkbook.Sheets(2).Cells(i, 4) = ""
ThisWorkbook.Sheets(2).Cells(i, 5) = ""
ThisWorkbook.Sheets(2).Cells(i, 6) = ""
ThisWorkbook.Sheets(2).Cells(i, 7) = ""
ThisWorkbook.Sheets(2).Cells(i, 8) = ""

Next i

ThisWorkbook.Sheets(3).Cells(1, 1) = ""
Application.OnTime Now + TimeValue("00:15:00"), "LoggingFunction",
Schedule:=False

End Sub

Thanks for any help.
Dale

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson