Timer / Countdown code?

Topic

New Reply

been trying to figure a way to run a countdown timer in a worksheet that will then trigger a macro at the end, also allowing for manual refresh of the timer and macro activation.

this is what i have so far, using 2 images linked to the start / stop subs, the refresh one is linked to a label on the worksheet,

Code:

 Public CountdownActive As Boolean
 Public RefreshReset As Boolean
'
Dim RefreshPeriod As Date
Dim CountdownPeriod As String
'


Sub Start_Countdown()
    RefreshPeriod = "00:00:01"                              ' Set Refresh Period here, use "hh:mm:ss" format
    CountdownPeriod = "00:05:00"                            ' Set Countdown Period Required, "hh:mm:ss" format
    Range("Countdown").Value = CountdownPeriod              ' Re-iterate cell value here incase of previous error
    ActiveSheet.Shapes("Auto_Refresh_On").Visible = True    ' Show On Switch
    ActiveSheet.Shapes("Auto_Refresh_Off").Visible = False  ' Hide Off Switch
    ActiveSheet.Shapes("Data_Refresh2").Visible = False     ' Hide the "Click To Refresh" label
    Application.OnTime Now() + TimeValue(RefreshPeriod), "Refresh_Trigger"
    CountdownActive = True
    RefreshReset = False
End Sub
'


Sub Stop_Countdown()
    ActiveSheet.Shapes("Auto_Refresh_Off").Visible = True   ' Show Off Switch
    ActiveSheet.Shapes("Auto_Refresh_On").Visible = False   ' Hide On Switch
    ActiveSheet.Shapes("Data_Refresh2").Visible = True      ' Show the "Click To Refresh" label
    On Error Resume Next
    Application.OnTime Now() + TimeValue(RefreshPeriod), "Refresh_Trigger", , False
    CountdownActive = False
    Range("Countdown").Value = CountdownPeriod
End Sub
'


Sub Refresh_Trigger()
    If CountdownActive Then
        Range("Countdown").Value = Range("Countdown").Value - TimeValue(RefreshPeriod)
        If Range("Countdown").Value = TimeValue("00:00:00") Or RefreshReset = True Then
            Application.StatusBar = "Updating Share Prices and Chart Data"
            GetData
            Application.StatusBar = " "
            Range("Countdown").Value = CountdownPeriod
        End If
        If RefreshReset = False Then
            Application.OnTime Now() + TimeValue(RefreshPeriod), "Refresh_Trigger"
        Else
            RefreshReset = False
        End If
    End If
End Sub

Whilst this works, it is far from elegant, and has some issues, mainly in that it pauses when your editing a cell or other such task, so as an actual timer it’s less than effective/efficient. Also, because of the way it works it constantly causes the cursor to show busy, and take focus form the worksheet, for example, comments will appear briefly at or around each refresh period cycle.

Does anyone know a better solution ? i’ve heard mention of form based timers ? also windows API versions, but as i’ve tried running one of those with little success, i’m not sure if that’s the way to go, also it’s in the same workbook !

Reply | Quote

Replies

it constantly causes the cursor to show busy, and take focus form the worksheet, for example, comments will appear briefly at or around each refresh period cycle.

You may consider using Do Events

Maud

Reply | Quote

You can’t run code while a user is editing a cell. Even a Windows API timer won’t get around that.

Reply | Quote

ok, so i tried slotting the DoEvents instruction in the Refresh_Trigger() Sub, just after the “If CountdownActive Then” line

not sure i grasp this function fully, anyway, it doesn’t do what i was looking for, whilst it does settle the cursor down, and stop it from flickering, and also slow the focusing issue, i still lose the comments after a very brief period, and they don’t come back on anymore (blink on then off then on effect)

Reply | Quote