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:
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
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.
Of course. I knew I had the conditions correct. My pseudo code helped me fail. Thanks for the reminder.