countdown timer, macro

ltr · Jun 18th 2016

Hi All,

Very new to this I have a figure in money terms in cell b1 which is changing all the time and I need to record the figure at certain intervals say 5m 2m 1m I have a countdown clock in c3 and I am trying to get the figures into cells a5,a6,and a7 with the times they were recorded in the b column I have tried with a formula but cant stop the figures changing as the time counts down or disappearing all together and was wondering if it is possible to do with vba and where I would start any help would be greatly appreciated thanks in advance.

mrmmickle1 · Jun 18th 2016

Re: countdown timer, macro

Maybe you can use this worksheet change event:

Code

Private Sub Worksheet_Change(ByVal Target As Range)


    If Format(Range("C3"), "hh:mm:ss") = "00:05:00" Then '5 minute mark
        Range("A5") = Now()
    ElseIf Format(Range("C3"), "hh:mm:ss") = "00:02:00" Then
        Range("A6") = Now()
    ElseIf Format(Range("C3"), "hh:mm:ss") = "00:01:00" Then
       Range("A7") = Now()
    End If
    
End Sub

Display More

ltr · Jun 18th 2016

Re: countdown timer, macro

Thanks for the reply it probably works just not for me am probably going to give up with vba can't get my head round it but thanks anyway

mrmmickle1 · Jun 18th 2016

Re: countdown timer, macro

You shouldn't give up. It's not to bad. Just do the following.

1. Use Alt + F11 to access the Visual Basic Editor
2. In the Project Explorer Pane on the left. Double Click the worksheet icon that your Timer is on.
3. Paste the code in the blank white space on the right.

ltr · Jun 20th 2016

Re: countdown timer, macro

Thank you I didn't give up I have customised it for what I needed, the only thing is there a limit to how many elseif statements you can have It now records the values every second for 5 minutes so 300 elseif when I try 10 mins it says it too long to compile would I be able to do it with a loop or is there a better way thank you again

mrmmickle1 · Jun 20th 2016

Re: countdown timer, macro

Can you please paste the first 10 or 20 lines of your code? or better yet attach an example file. I'm sure there's a simpler way to accomplish your goal.

ltr · Jun 21st 2016

Re: countdown timer, macro

forum.ozgrid.com/index.php?attachment/69487/

I have attached a file with the code or do you want the spreadsheet the problem with the spreadsheet is it connects to an API and that's the data I am trying to seperate

mrmmickle1 · Jun 22nd 2016

Re: countdown timer, macro

Does the Event Fire when every second changes or not? Does the code you wrote work if you cut it down to fewer lines??

If so you may try something much simpler like this:

Code

Private Sub Worksheet_Change(ByVal Target As Range)


   Dim LastRow As Long
    
    LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & LastRow + 1) = Range("a1")
    Range("B" & LastRow + 1) = Range("e1")'TimeStamp????




End Sub

Display More

If the event is firing every second then this code should log a value in Column A as well as the timestamp in Column B.... i.e. "00:05:00", "00:04:59"...etc

countdown timer, macro

Participate now!

Share

Users Viewing This Thread