-
Solved: Help modifying excel timer
Hi all,
I am very new to using VBA in excel. I have been trying to create a timer on excel that can be embedded several times in the same sheet and can run independent from each other. I found the following code on another webpage that allows me to embed a timer in a single cell and counts down after you tell it what the expiration time is.
example = current time is noon, and I want the expiration time to 1300 hours, so I put in 1300 in the formula and the timer starts to count down from 2:00:00 (2 hours).
This is exactly what I needed, but if you set a time for 3am and it is 9pm currently the timer just sets itself at 0:0:0. I was wondering if there is anything that can be changed in the code to avoid this. I was also wondering if there is a way to add in a function that will turn the timer red when there is one hour left on the countdown.
I will post the code below and would appreciate any help. I knew some basic c++ back in the day but I have no experience doing this. Let me know if there is any more information I can provide to help. Also this is for a completely non-profit project.
The code is this (instructions between lines, must be removed to run)
in the project explorer window (open that window using the "View" menu if it isn't open already), locate your file and double click on it.
- Choose "Insert, Class module" from the menu
- Open the properties window (View, properties) and change the Name of the class (now reads "Class1") to clsTimer.
- IN the code window of clsTimer paste this code:
[VBA]Option Explicit
Private msCellAddress As String
Private mdtEndTime As Date
Public Property Get CellAddress() As String
CellAddress = msCellAddress
End Property
Public Property Let CellAddress(ByVal sCellAddress As String)
msCellAddress = sCellAddress
End Property
Public Property Get EndTime() As Date
EndTime = mdtEndTime
End Property
Public Property Let EndTime(ByVal dtEndTime As Date)
mdtEndTime = dtEndTime
End Property[/VBA]
- Insert a module (Insert, module from the menu)
- paste in this code:
[VBA]Option Explicit
Private mdNextTime As Double
Private mcolTimers As Collection
Public Function CountDown(EndTime As Date) As Variant
Dim cTimer As clsTimer
Dim sCellAddress As String
sCellAddress = Application.Caller.Address(external:=True)
Application.Volatile
If TypeName(Application.Caller) <> "Range" Then
Else
If IsIn(mcolTimers, sCellAddress) Then
Set cTimer = mcolTimers(sCellAddress)
mcolTimers.Remove sCellAddress
Else
Set cTimer = New clsTimer
With cTimer
.CellAddress = sCellAddress
.EndTime = EndTime
End With
End If
End If
With cTimer
.CellAddress = sCellAddress
.EndTime = EndTime
If .EndTime - (Now - Int(Now)) > 0 Then
CountDown = .EndTime - (Now - Int(Now))
Else
CountDown = 0
End If
End With
If mcolTimers Is Nothing Then
Set mcolTimers = New Collection
End If
mcolTimers.Add cTimer, sCellAddress
Set cTimer = Nothing
End Function
Private Function IsIn(colCollection As Collection, sName As String)
Dim cTimer As clsTimer
On Error Resume Next
Set cTimer = mcolTimers(sName)
IsIn = (Err.Number = 0)
Set cTimer = Nothing
End Function
Public Sub Auto_Open()
Application.OnTime Now, "UpdateTimers"
End Sub
Public Sub Auto_Close()
StopTimers
End Sub
Sub UpdateTimers()
mdNextTime = Now + TimeValue("00:00:01")
Application.OnTime mdNextTime, "UpdateTimers"
Application.Calculate
End Sub
Sub StopTimers()
Application.OnTime mdNextTime, "UpdateTimers", , False
End Sub[/VBA]
- Now in any cell, enter a formula like this:
=CountDown(Time(16,20,00))
and format the cell as time.
- Save your file, close it and open it again.
The cell should start to count down to 00:00 (which it reaches at whatever time you entered).
I thank you in advance for any help,
LD
Last edited by Bob Phillips; 07-28-2011 at 12:49 AM.
Reason: Added VBA tags
-
Change:
[vba]If .EndTime - (Now - Int(Now)) > 0 Then
CountDown = .EndTime - (Now - Int(Now))
[/vba] to:
[vba] If .EndTime - Now > 0 Then
CountDown = .EndTime - Now [/vba]
Change the format of the cell to:
[h]:mm:ss
Include date info in the cell:
eg.:
=countDown(DATE(2011,7,29)+TIME(17,10,0))
and see other suggestion in C5 below.
I've included conditional formatting to go red in the last hour (0.04166666) and to go grey at expiry.
2011-07-29_175156.jpg
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
Thank you so much for your help p45cal, I really appreciate it! If I have further questions I will let you know. thanks again!
LD
-
moderator, please change the title of this post to solved. i can't find a way to do it. my issue was resolved and i thank all of those who helped out!
-
hi, i have issue with copy and paste in same sheet where im using this macro, could u pls help why is it so, or should i also send you my xlsm file?
-
You should be able to attach it here.
What is the 'issue'?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
during countdown process in sheet, icant copy and paste other cells, when i turn off makro, it seems everything is well, but when i turn it on again, ist unfunctional, i need much copy and paste during my work
-
Not much I can do about that, however, if you change the line:
mdNextTime = Now + TimeValue("00:00:01")
to
mdNextTime = Now + TimeValue("00:00:20")
which will change the update frequency of timers to 20 seconds from 1 second. Of course, you could enter an even longer period (minutes?). This might give you time to do your copy/pasting between timer updates if you can live with the slower timer updates. It will still catch you out if you're copying/pasting at the same time as it's doing its updating.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules