VBA End Application.Ontime Macro

[Fobling]

Morning all,

I'm not a VBA pro, so you can imagine my delight when I managed to auto start a macro, that made the test in one of my cells "blink". However, the file now opens everytime I close it - because I haven't ended my Application.OnTime and I just can't get my head round it.

So, in my "ThisWorkbook" my code looks like:

Private Sub workbook_Open()
Call StartBlink
End Sub

And in my module, the corresponding code looks like:

Sub StartBlink()
    Dim xCell As Range
    Dim xTime As Variant
    Set xCell = ThisWorkbook.Worksheets("VOC -1").Range("M5")
    With ThisWorkbook.Worksheets("VOC -1").Range("M5").Font
        If xCell.Font.Color = vbRed Then
            xCell.Font.Color = vbWhite
        Else
            xCell.Font.Color = vbRed
        End If
    End With
    xTime = Now + TimeSerial(0, 0, 0.75)
    Application.OnTime xTime, "'" & ThisWorkbook.Name & "'!StartBlink", , True


End Sub

How do I make it stop when I close the file?

Thanks,

Fi

 

[RoryA]

Welcome to the forum.

I recommend you read the late Chip Pearson's article on OnTime here: http://www.cpearson.com/Excel/OnTime.aspx

 

[joeu2004]

Add the following to the ThisWorkbook object:

Private Sub Workbook_BeforeClose()
    Call StopBlink
End Sub

And make the following changes in the module that constains StartBlink.

Dim xTime As Variant

Sub StartBlink()
    Dim xCell As Range
    [.... remove Dim xTime ....]
  
    [....]
    xTime = Now + TimeSerial(0, 0, 0.75)
    Application.OnTime xTime, "'" & ThisWorkbook.Name & "'!StartBlink", , True
End Sub

Sub StopBlink()
    On Error Resume Next
    Application.OnTime xTime, "StartBlink",, False
End Sub

It should not be necessary to prefix the sub name (StartBlink) with the workbook name.

Also, I believe that TimeSerial(0,0,0.75) is rounded to TimeSerial(0,0,1). Use 0.75/86400 instead.

But note that Now() is truncated to the current second. So the first OnTime event might occur sooner than expected.