Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Question Timer / Countdown code?

    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 !

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can't run code while a user is editing a cell. Even a Windows API timer won't get around that.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    May 2016
    Posts
    50
    Thanks
    9
    Thanked 4 Times in 4 Posts
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •