countdown timer

[0lzi]

here is a picture to help visualise what im trying to do.

im trying to get the system time, then getting time from the user from inputbox, then display the difference in a shapes text box and loop untill the timeleft = 0

code i have so far:

Sub RoundedRectangle1_Click()

Dim setTimer
Dim currentTime
Dim timeLeft As Single

setTimer = InputBox("Please enter a Time seperated by : eg. hr:min:sec")

currentTime = Time

timeLeft = setTimer - currentTime

With ActiveSheet.Shapes("Rounded Rectangle 1") _
.TextFrame.Characters.Text = ("& timeLeft &")
End With

End Sub

i have not included a loop yet i am just wanting to display the time difference, between the two times.

 

[Andrew Poulsom]

This worked for me:

Sub RoundedRectangle1_Click()
    Dim setTimer
    Dim currentTime As Date
    Dim timeLeft As Date
    setTimer = InputBox("Please enter a Time seperated by : eg. hr:min:sec")
    Do
        currentTime = Time
        timeLeft = TimeValue(setTimer) - currentTime
        With ActiveSheet.Shapes("Rounded Rectangle 1")
            .TextFrame.Characters.Text = "" & timeLeft & ""
        End With
        Application.Wait Now + TimeValue("00:00:01")
    Loop While timeLeft > TimeValue("00:00:00")
End Sub

 

[0lzi]

thanks :D

is there a better way of doing this ? because when i look in taskmanager, EXCEL.EXE is using 25% CPU.

its probably because its running the loop as fast as it can and pausing for a second.

i also still need a break for when i want to close the application, or have a pause button or somthing.

also is there a way of starting that sub when the file opens, and for the window to be a certain size?

sorry for all the questions

 

[Andrew Poulsom]

An OnTime procedure may be less processor intensive.

You can stop the macro by pressing Esc or Ctrl+Break.

You can call the procedure from the Workbook_Open event procedure.

If you record a macro while sizing the window you will get some VBA code.