Warm tip: This article is reproduced from serverfault.com, please click

Excel Timer with banner that changes in segments

发布于 2020-11-28 15:45:04

Trying to make a banner that changes through different segments of an allotted time. The different segments are 15, 20, 25 minutes. I have a setup page that the user can select the segment from a combobox. Then displays the workflow page Like so:

enter image description here

This is a sample of what the banner should do for a 15 minute segment (pseudo code):

        When it starts (15 remaining)
            TimeBanner = "Thinking Time"
        After 1 minute (14 remaining) 
            TimeBanner = "Response Time"
        after 10 minutes (3 remaining)
            TimeBanner = "Refine and Improve"
        after 14 minutes(1 remaining) 
            TimeBanner = "Submit"
        after 15 minutes (0 remaining)
            TimeBanner = "Time Up"
       

The timer works, but the the banner doesn't change So I thought perhaps I just needed to add DoEvents, but it doesn't change

Here's the code

Option Explicit

 Public interval As Date
 Sub timer()

     interval = Now + TimeValue("00:00:01")

     If Sheets("VSS").Range("D1").Value = 0 Then Exit Sub
     
    'Show the time elapsed
    Sheets("VSS").Range("D1").Value = Sheets("VSS").Range("D1").Value - TimeValue("00:00:01")
    DoEvents

    Sheets("VSS").Range("E3").Value = TimeBanner(Sheets("ControlData").Range("A7").Value, Minute(Sheets("VSS").Range("D1").Value))
    DoEvents
    
    'Show the Time Banner matching the Elapsed Time
    
   'MsgBox Minute(Sheets("VSS").Range("D1").Value)

   Application.OnTime interval, "timer"
   
   
   DoEvents

 End Sub

 Sub stop_timer()
        'Only allow 'Stop' if the timer has started
        
        Dim iMin As Integer
        iMin = Left(Format(Sheets("VSS").Range("D1").Value, "mm:ss"), 2)
        
        
     'i.e. if the time elapsed DOES NOT equal whatever they chose
     'It means that the timer has started
     
     If iMin <> Sheets("ControlData").Range("A7").Value Then
        Application.OnTime EarliestTime:=interval, Procedure:="timer", Schedule:=False
     End If
 End Sub

Sub reset_timer()
    Sheets("VSS").Range("D1").Value = "00:" & Sheets("ControlData").Range("A7").Value & ":00"
End Sub


Function TimeBanner(iTimeChosen As Integer, iElapsedTime As Integer) As String

Debug.Print "Time chosen: " & iTimeChosen & ", Elapsed " & dtElapsedTime

Select Case iTimeChosen

    Case 25
    
        Select Case iElapsedTime
            Case Is <= 25
                TimeBanner = "Thinking Time"
            Case Is <= 23
                TimeBanner = "Response Time"
            Case Is <= 5
                TimeBanner = "Refine and Improve"
            Case Is <= 1
                TimeBanner = "Submit"
            Case 0
                TimeBanner = "Time Up"
        End Select
        
    Case 20
            Select Case iElapsedTime
            Case Is <= 20
                TimeBanner = "Thinking Time"
            Case Is <= 18
                TimeBanner = "Response Time"
            Case Is <= 4
                TimeBanner = "Refine and Improve"
            Case Is <= 1
                TimeBanner = "Submit"
            Case Is = 0
                TimeBanner = "Time Up"
            End Select
    
    Case 15
            Select Case iElapsedTime
            Case Is <= 15
                TimeBanner = "Thinking Time"
            Case Is <= 14
                TimeBanner = "Response Time"
            Case Is <= 3
                TimeBanner = "Refine and Improve"
            Case Is <= 1
                TimeBanner = "Submit"
            Case Is = 0
                TimeBanner = "Time Up"
            End Select
End Select

End Sub
Questioner
plateriot
Viewed
0
Chris Strickland 2020-11-29 02:38:41

Reverse the order of your case conditions. Case Is <= 25, for instance, will always fire for any number 25 and down, which is all of them, so your first condition is always executed.