[Excel VBA] How to Display a Countdown Timer in a Shape

2025年12月30日

Using Excel VBA, you can display a countdown timer directly inside a shape on your worksheet.

In this article, we will create a system that displays “Remaining Time: 00:00:00” inside a shape and updates it in real-time every second.

Goal

Place a shape (e.g., a rectangle) on an Excel worksheet.
Display a countdown inside the shape that updates every second.
Display “Time’s up!” when the counter reaches zero.

Steps and Preparation

1. Insert a Shape into the Worksheet

Go to Insert > Shapes and add a shape, such as a rectangle or oval, to your worksheet.

2. Name the Shape

This step is crucial. Select the shape you just created. In the Name Box at the top left of the screen (to the left of the Formula Bar), type the following name and press Enter:

CountdownShape

3. Add the VBA Code

Press Alt + F11 to open the VBA Editor. Insert a Standard Module (e.g., Module1) and paste the code below.

VBA Code

Dim Countdown As Date

Sub StartTimer()
    ' Set the timer end time (e.g., 10 minutes from now)
    Countdown = Now + TimeValue("00:10:00")
    ' Call UpdateTimer for the first time (after 1 second)
    Application.OnTime Now + TimeValue("00:00:01"), "UpdateTimer"
End Sub

Sub UpdateTimer()
    Dim RemainingTime As String

    If Now >= Countdown Then
        ' Display when time is up
        ' Note: Change Sheet1 to Worksheets("YourSheetName") if necessary
        Sheet1.Shapes("CountdownShape").TextFrame.Characters.Text = "Time's up!"
    Else
        ' Calculate remaining time in hh:mm:ss format
        RemainingTime = Format(Countdown - Now, "hh:mm:ss")
        ' Display remaining time in the shape
        Sheet1.Shapes("CountdownShape").TextFrame.Characters.Text = "Remaining Time: " & RemainingTime
        ' Schedule the next update (after 1 second)
        Application.OnTime Now + TimeValue("00:00:01"), "UpdateTimer"
    End If
End Sub

Explanation of the Code

StartTimer

This subroutine starts the countdown. It stores the target end time (set to 10 minutes from now in this example) in the Countdown variable and uses Application.OnTime to schedule the UpdateTimer subroutine to run after 1 second.

UpdateTimer

This is the main process that runs every second. It compares the current time with the Countdown time to calculate the remaining duration. It updates the text of the shape using Sheet1.Shapes("CountdownShape"). If time remains, it calls itself (UpdateTimer) again after 1 second using OnTime, creating a loop.

Important Notes

Sheet Reference: The code uses Sheet1, which is the VBA Code Name. If you have renamed your sheet or want to target a specific sheet by its tab name, replace Sheet1 with Worksheets("SheetName").
Shape Name: If the shape name in the Excel Name Box does not match the code (CountdownShape), an error will occur.
Stopping: The OnTime timer will stop if you close Excel or if the VBA code encounters an error and enters debug mode.