-2

I have to write a macro code to get the given time as input in the format (HH:MM:SS). and then it should run the time in background at the end of the time it should display a message as "time over". after giving some input or time again it restarts from the given time. kindly suggest

vicky
  • 5
  • 1
  • 9
  • This local article [How do I show a running clock in Excel?](http://stackoverflow.com/questions/11867225/how-do-i-show-a-running-clock-in-excel) would be a good place to start. –  Nov 03 '14 at 04:10

1 Answers1

0
  1. Choose a cell and name it as rngElapseTime
  2. Format the rngElapseTime cell as Time with the Type as HH:MM:SS
  3. Go to Developer --> ViewCode
  4. Copy and paste the code below.

That's it. Every time, a user changes the value in the rngElapseTime, the timer in the code below restarts and will prompt user with TimeOver when it times up.

Private Sub Worksheet_Change(ByVal Target As Range)    
    If Target.Address = Range("rngElapseTime").Address Then        
     nextScheduledTime = Now + TimeValue(Format(Target.Value, "HH:MM:SS"))
     Application.OnTime nextScheduledTime, "TimeOver"    
    End If            
End Sub


Public Sub TimeOver()    
    MsgBox "Time Over", vbInformation            
End Sub
CLL
  • 26
  • 2