Countdown timer VBA macro script for a judo scoreboard

[rcarvalho]

I'm looking for a VBA macro script for a countdown timer with just minutes and seconds (no hours) for my judo scoreboard.

The time (in intervals of 10 seconds to 10 minutes) to be counted must be defined in a specific cell, in a separate settings sheet, as combat time varies according to age groups.

The timer requires being able to be paused, stopped, restarted and reset via buttons.

The end of the countdown should serve as a trigger to activate a sound signal (specific wave file within the same folder as my xlsm file) and determine the winner of the fight through some logical output in a cell (e.g. white wins = 1 / blue wins = 0), depending on the score of each fighter recorded on the panel (the sum of the score is done separately using formulas).

Additionally, when the fight takes place in "Golden Score" mode, a checkbox will be activated manually on the panel and the count must become progressive (upwards) with no time limit. As in "Golden Score" mode the winner will be determined by the first competitor to score, it will be necessary for the timer to be stopped manually using the same count stop button, but with the same audible sound signal activated when the countdown time reaches the end.

I've read multiple posts here but couldn't find any suitable code for my judo scoreboard. Can anyone support? Unfortunately, I don't have enough knowledge of VBA and I usually just adapt the code to my needs. Thank you very much in advance.

 

[Rhodie72]

I hope this does what I wrote it to do. First, make sure you have a Settings Sheet where the combat time in minutes is set (e.g., cell A1 contains the initial countdown time). You will also need a checkbox for Golden Score mode.

Dim CountdownTime As Integer ' Store the initial countdown time (in seconds)
Dim TimerRunning As Boolean ' Flag to track if the timer is running
Dim GoldenScoreMode As Boolean ' Flag to track if Golden Score mode is on
Dim CurrentTime As Integer ' Current time for countdown (in seconds)
Dim StartTime As Integer ' Store the original start time
Dim CountDownTimer As Double ' Timer that controls the countdown
Sub StartTimer()
    Dim combatTime As Integer
    ' Get combat time from the settings sheet (in minutes, converted to seconds)
    combatTime = ThisWorkbook.Sheets("Settings").Range("A1").Value * 60
    ' You may want to use the system time to set a target time
    'which is not impossible. Different computers have different processing speeds
    'so time calculation may differ depending on resource use too.

    If GoldenScoreMode Then
        ' In Golden Score mode, count up from 0
        CurrentTime = 0
        CountdownTime = 999999 ' Infinite countdown

    Else
       
        CurrentTime = combatTime ' Normal countdown mode
        CountdownTime = combatTime

    End If
    TimerRunning = True
    Call UpdateTimerDisplay
    CountDownTimer = Application.OnTime(Now + TimeValue("00:00:01"), "TimerTick")
End Sub
Sub TimerTick()
    If Not TimerRunning Then Exit Sub
   
    ' Select Case for Golden Score mode
    Select Case GoldenScoreMode
        Case False ' Normal countdown mode
            ' Check if there is still time remaining
            If CurrentTime > 0 Then
                CurrentTime = CurrentTime - 1
            Else
                ' Timer ended, trigger the end sound
                Call TimerEnded
            End If

        Case True ' Golden Score mode
            ' Golden Score mode, count up
            CurrentTime = CurrentTime + 1
       
        Case Else
            ' This can be a fallback or error handling case if needed
            MsgBox "Unexpected mode"
    End Select

    ' Update the display
    Call UpdateTimerDisplay
    ' Continue the countdown
    CountDownTimer = Application.OnTime(Now + TimeValue("00:00:01"), "TimerTick")
End Sub

Sub StopTimer()
    ' Stop the timer and play sound
    TimerRunning = False
    On Error Resume Next
        Application.OnTime CountDownTimer, "TimerTick", , False
    On Error GoTo 0
    Call TimerEnded
End Sub
Sub PauseTimer()
    ' Pause the timer
    TimerRunning = False
End Sub
Sub ResetTimer()

    ' Reset the timer to the original value
    If Not GoldenScoreMode Then
        CurrentTime = CountdownTime

    Else
        CurrentTime = 0

    End If
    Call UpdateTimerDisplay
End Sub
Sub TimerEnded()
    ' Play the sound when the timer ends (use a specific .wav file in the same directory as the Excel file)
    Dim soundPath As String

    soundPath = ThisWorkbook.Path & "\alarm.wav"
    ' Play sound (Windows Script Host Object Model needs to be enabled in References)
    Call PlaySound(soundPath)
    ' Determine the winner (logic based on the scores of both fighters)
    Call DetermineWinner
End Sub
Sub PlaySound(soundPath As String)
    ' Play the sound using Windows Script Host (you may need to enable "Microsoft Windows Script Host Object Model" in References)
    Dim objShell As Object
    Set objShell = CreateObject("WScript.Shell")

    objShell.Run "cmd /c start """" """ & soundPath & """", 0, False

    Set objShell = Nothing
End Sub
Sub UpdateTimerDisplay()
    ' Update the display on the sheet with the remaining time in minutes and seconds
    Dim Minutes As Long, Seconds As Long, DisplayTime As String

    Minutes = Int(CurrentTime / 60)
    Seconds = CurrentTime Mod 60
    DisplayTime = Format(Minutes, "00") & ":" & Format(Seconds, "00")
    ThisWorkbook.Sheets("Scoreboard").Range("B1").Value = DisplayTime ' Update the timer display cell
End Sub
Sub DetermineWinner()
    ' Logic to determine the winner based on scores (for example, using cells like B2 and B3 for blue and white scores)
    Dim WhiteScore As Long, BlueScore As Long
    With ThisWorkbook.Sheets("Scoreboard")
        WhiteScore = .Range("B2").Value
        BlueScore = .Range("B3").Value
   
        Select Case WhiteScore
            Case Is > BlueScore
                .Range("B4").Value = "White wins"
       
            Case Is < BlueScore
                .Range("B4").Value = "Blue wins"
       
            Case Else
                .Range("B4").Value = "Draw"
        End Select
    End With
End Sub
Sub ToggleGoldenScoreMode()
    ' Toggle Golden Score mode on/off
    With ThisWorkbook.Sheets("Scoreboard").Range("A5")
        GoldenScoreMode = Not GoldenScoreMode
        If GoldenScoreMode Then
            ' Turn on Golden Score mode
            .Value = "Golden Score"
        Else
            ' Turn off Golden Score mode
            .Value = "Normal"
        End If
    End With
End Sub

If you are really smart, you can create a userform that acts like a standalone application instead of using spreadsheets for dynamic updates. That's a little more complex but so much more user-friendly than a spreadsheet and you'll get some golden looks for your efforts.

To integrate this timer functionality into a UserForm in Excel, you'll need to take several steps. The following process shows how you can use the code inside a UserForm to control the timer, pause, stop, reset, and toggle between normal and Golden Score modes.
Steps:

Create a UserForm with necessary controls:
Add labels, text boxes, buttons, and checkboxes to the form:
Timer Display: A label to show the countdown (minutes and seconds).
Start Button: To start the timer.
Pause Button: To pause the timer.
Stop Button: To stop the timer and play the sound.
Reset Button: To reset the timer.
Golden Score Toggle: A checkbox to toggle Golden Score mode.

Link VBA code to the UserForm: We'll modify the provided VBA code so that it operates within the UserForm environment. Here's how you can integrate your timer logic into the UserForm.
Code to put in UserForm1:

Dim CountdownTime As Integer ' Store the initial countdown time (in seconds)
Dim TimerRunning As Boolean ' Flag to track if the timer is running
Dim GoldenScoreMode As Boolean ' Flag to track if Golden Score mode is on
Dim CurrentTime As Integer ' Current time for countdown (in seconds)
Dim StartTime As Integer ' Store the original start time
Dim CountDownTimer As Double ' Timer that controls the countdown

' This is the UserForm's Initialize event, setting up initial values
Private Sub UserForm_Initialize()
    ' Initialize the timer with the combat time from the settings sheet
    Dim combatTime As Integer
    combatTime = ThisWorkbook.Sheets("Settings").Range("A1").Value * 60 ' Combat time in seconds (from A1)
    CountdownTime = combatTime ' Store initial time for countdown
    CurrentTime = CountdownTime ' Set current time to combat time
    GoldenScoreMode = False ' Default to normal mode
    TimerRunning = False ' Timer is initially not running
    UpdateTimerDisplay
End Sub

' Start the timer
Private Sub StartButton_Click()
    If TimerRunning Then Exit Sub
    ' Start Timer
    TimerRunning = True
    Call UpdateTimerDisplay
    CountDownTimer = Application.OnTime(Now + TimeValue("00:00:01"), "TimerTick")
End Sub

' Timer Tick event (called every second)
Sub TimerTick()
    If Not TimerRunning Then Exit Sub
   
    ' Select Case for Golden Score mode
    Select Case GoldenScoreMode
        Case False ' Normal countdown mode
            ' Check if there is still time remaining
            If CurrentTime > 0 Then
                CurrentTime = CurrentTime - 1
            Else
                ' Timer ended, trigger the end sound
                Call TimerEnded
            End If

        Case True ' Golden Score mode
            ' Golden Score mode, count up
            CurrentTime = CurrentTime + 1
       
        Case Else
            ' This can be a fallback or error handling case if needed
            MsgBox "Unexpected mode"
    End Select

    ' Update the display
    Call UpdateTimerDisplay
    ' Continue the countdown
    CountDownTimer = Application.OnTime(Now + TimeValue("00:00:01"), "TimerTick")
End Sub

' Stop the timer and play sound
Private Sub StopButton_Click()
    TimerRunning = False
    On Error Resume Next
        Application.OnTime CountDownTimer, "TimerTick", , False
    On Error GoTo 0
    Call TimerEnded
End Sub

' Pause the timer
Private Sub PauseButton_Click()
    TimerRunning = False
End Sub

' Reset the timer to the initial value
Private Sub ResetButton_Click()
    If Not GoldenScoreMode Then
        CurrentTime = CountdownTime
    Else
        CurrentTime = 0
    End If
    Call UpdateTimerDisplay
End Sub

' Timer ended: Play the sound and determine the winner
Sub TimerEnded()
    ' Play the sound when the timer ends (use a specific .wav file in the same directory as the Excel file)
    Dim soundPath As String
    soundPath = ThisWorkbook.Path & "\alarm.wav"
    ' Play sound
    Call PlaySound(soundPath)
    ' Determine the winner
    Call DetermineWinner
End Sub

' Play the sound using Windows Script Host
Sub PlaySound(soundPath As String)
    Dim objShell As Object
    Set objShell = CreateObject("WScript.Shell")
    objShell.Run "cmd /c start """" """ & soundPath & """", 0, False
    Set objShell = Nothing
End Sub

' Update the display on the UserForm with the remaining time
Sub UpdateTimerDisplay()
    Dim Minutes As Long, Seconds As Long, DisplayTime As String
    Minutes = Int(CurrentTime / 60)
    Seconds = CurrentTime Mod 60
    DisplayTime = Format(Minutes, "00") & ":" & Format(Seconds, "00")
    TimerDisplayLabel.Caption = DisplayTime ' Assuming TimerDisplayLabel is a Label control in the UserForm
End Sub

' Logic to determine the winner based on scores
Sub DetermineWinner()
    Dim WhiteScore As Long, BlueScore As Long
    With ThisWorkbook.Sheets("Scoreboard")
        WhiteScore = .Range("B2").Value
        BlueScore = .Range("B3").Value
   
        Select Case WhiteScore
            Case Is > BlueScore
                .Range("B4").Value = "White wins"
       
            Case Is < BlueScore
                .Range("B4").Value = "Blue wins"
       
            Case Else
                .Range("B4").Value = "Draw"
        End Select
    End With
End Sub

' Toggle Golden Score mode on/off
Private Sub GoldenScoreCheckBox_Click()
    GoldenScoreMode = Not GoldenScoreMode
    If GoldenScoreMode Then
        GoldenScoreLabel.Caption = "Golden Score Mode"
    Else
        GoldenScoreLabel.Caption = "Normal Mode"
    End If
End Sub

And then run your userform. You can make the form do all sorts of intersting things like change colour to gold if you want!
You can add images as backgrounds and you can really make it look fantastic just to blow the minds of people who know you and Excel programming. Userforms are not used often enough in VBA.

Now go play nicely!

 

[rcarvalho]

Dear @Rhodie72, many thanks for your reply and effort. I will take some time following your instructions and tryout the code you sent. also to use a userform sound great. Hopefully I can manage and create a stunning judo scoreboard. Cheers.