Countdown Timer Advanced

I have a Timer that Professor JimJam wrote from EE to help me with a project.  I need another enhancement.  Can someone take a look at how I can, on the initial WS, designate an input that would include not only seconds, but Minutes and Hours with the appropriate countdown in Minutes and or Seconds?

Thank you in advance.

B.
Countdown-Timerv2.xlsb
Bright01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Jim,
I modified the code so the cell would display the remaining time in [h]:mm:ss format, updating every second. I also played around with the text displayed on the button--which is now a Forms button rather than the ActiveX button that morphed its shape on me when I clicked it.

Brad

Sub cmdStop_Click()
Dim shp As Shape
Set shp = ActiveSheet.Shapes(Application.Caller)
With shp.TextFrame.Characters
    If .Text = "Timer: Off" Then
        Call Stopper
        .Text = "Timer: Resume"
    
    ElseIf .Text = "Timer: Resume" Then
        .Text = "Timer: Off"
        Call Starter(True)
    
    ElseIf .Text = "Timer: Start" Then
        .Text = "Timer: Off"
        Call Starter(False)
    
    Else
        .Text = "Timer: Off"
        Call Starter(False)
        ActiveSheet.Range("A2").Select
    End If
End With
End Sub

Sub Starter(bResume As Boolean)
    Status = True
    Dim WarningTime As Integer
    Dim Period As Double
    Dim MyTime As Double
    Dim Countdown As Double
    
    With Sheets("Main")
        If (.Cells(5, 1) = "") Then
            WarningTime = .Cells(5, 4)
        Else
            WarningTime = .Cells(5, 1)
        End If
        
        If (.Cells(8, 1) = "") Then
            Period = .Cells(8, 4)
        Else
            Period = .Cells(8, 1)
        End If
    End With
    
    If (Period < 0.01) Then Period = 0.01
    
    Sheets("Counter").Select
    With Sheets("Counter").Cells(2, 1)
        .Select
        .FormatConditions.Delete
        .FormatConditions.Add xlCellValue, xlLessEqual, WarningTime
        With .FormatConditions(1).Font
            .Bold = True
            .ColorIndex = 3
        End With
        
        '.NumberFormat = Choose(Log(Period) / Log(10) + 3, "0.00", "0.0", "0")
        .NumberFormat = "[h]:mm:ss"
    
        If bResume = False Then .Value = (Sheets("Main").Cells(2, 1).Value + Period) / 86400
        Countdown = .Value * 86400
        While (Countdown > Period And Status)
            Countdown = Countdown - Period 'Time remaining in seconds
            .Value = Countdown / 86400
            MyTime = Countdown
            For i = 1 To 100 * Period
                Sleep 10
                MyTime = MyTime - 0.01
                If (MyTime <= 0) Then Exit For
                DoEvents
            Next i
        Wend
        If (Countdown <= Period) Then
            .Value = "Time Up!"
            ActiveSheet.Shapes("Button 3").TextFrame.Characters.Text = "Timer: Start"
        End If
    End With
End Sub

Open in new window

Countdown-Timerv2.xlsb
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
thanks byundt.  you definitely made this much better.

for the finishing touches, i have made a small modification in Mainsheet uploaded two versions as final

V3  added in Main Sheet C1 E1 D1 to be filled numeric hours minutes seconds cells highlighted yellow .

V4 added in Main Sheet C1 only to be put time as the Excel timer format cell highlighted yellow.
Countdown-Timerv3.xlsb
Countdown-Timerv4.xlsb
0
byundtMechanical EngineerCommented:
I noticed that the Conditional Formatting for the Warning wasn't happening in ProfessorJimJam's V4 workbook, so I modified the bits involving WarningTime to remedy that situation. This involved changing the variable to a Double, dividing the value from the worksheet by 86400 (number of seconds in a day), and referencing column C rather than D to get its value.

FWIW, the V3 workbook needed the exact same changes to the code. Just use the Starter sub as shown below in both workbooks.
Sub Starter(bResume As Boolean)
    Status = True
    Dim WarningTime As Double
    Dim Period As Double
    Dim MyTime As Double
    Dim Countdown As Double
    
    With Sheets("Main")
        If (.Cells(5, 1) = "") Then
            WarningTime = .Cells(5, 3) / 86400
        Else
            WarningTime = .Cells(5, 1) / 86400
        End If
        
        If (.Cells(8, 1) = "") Then
            Period = .Cells(8, 4)
        Else
            Period = .Cells(8, 1)
        End If
    End With
    
    If (Period < 0.01) Then Period = 0.01
    
    Sheets("Counter").Select
    With Sheets("Counter").Cells(2, 1)
        .Select
        .FormatConditions.Delete
        .FormatConditions.Add xlCellValue, xlLessEqual, WarningTime
        With .FormatConditions(1).Font
            .Bold = True
            .ColorIndex = 3
        End With
        
        '.NumberFormat = Choose(Log(Period) / Log(10) + 3, "0.00", "0.0", "0")
        .NumberFormat = "[h]:mm:ss"
    
        If bResume = False Then .Value = (Sheets("Main").Cells(2, 1).Value + Period) / 86400
        Countdown = .Value * 86400
        While (Countdown > Period And Status)
            Countdown = Countdown - Period 'Time remaining in seconds
            .Value = Countdown / 86400
            MyTime = Countdown
            For i = 1 To 100 * Period
                Sleep 10
                MyTime = MyTime - 0.01
                If (MyTime <= 0) Then Exit For
                DoEvents
            Next i
        Wend
        If (Countdown <= Period) Then
            .Value = "Time Up!"
            ActiveSheet.Shapes("Button 3").TextFrame.Characters.Text = "Timer: Start"
        End If
    End With
End Sub

Open in new window

Countdown-Timerv4.xlsb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bright01Author Commented:
Great job guys!  This is workable. Great Teamwork from both of you in getting this just right.

Thank you!

B.
0
ProfessorJimJamMicrosoft Excel ExpertCommented:
you are welcome Bright01.

byundt,  hit the nail on the head.

i just did one last small change to the last code posted by byundt

changing the line on sub routine Starter from 
ActiveSheet.Shapes("Button 3").TextFrame.Characters.Text = "Timer: Start"

Open in new window

To
        ThisWorkbook.Sheets("Counter").Shapes("Button 3").TextFrame.Characters.Text = "Timer: Start"
        ThisWorkbook.Activate

Open in new window


if the Counter worksheet wasn't active sheet while the timer is ended, it would have thrown an error. so now, even if you working in another workbook or in another sheet, when the timer ends, it will not give error.

i have uploaded both versions. v4 and v3.
Best-Countdown-Timer-V4.xlsb
Best-Countdown-Timer-V3.xlsb
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.