Announcement

Collapse
No announcement yet.

Update Time Automatically

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Update Time Automatically

    I have a cell (H2) that contains the formula =NOW()

    I would like this cell's time to be updated every minute automatically, with a Macro that will start itself the moment the spreadsheet is opened.

    I have attempted to find other users with the same question and have attempted to use Macro's supplied however without success. I have previously found this piece of code:

    Code:
    Sub UpdateTime() 
        Application.OnTime Now + TimeValue("00:01:00"), "TimeUp" 
         
    End Sub 
    Sub TimeUp() 
        [a1] = Time 
    End Sub
    However when I enter this into a module it only seems to update once.

    I hope I have quoted the above code correctly in line with thread rules. I'm a little bit new round here.

    Hope you can help.

    Kind regards

    RUSS999
    Last edited by royUK; April 25th, 2007, 21:54.

  • #2
    Re: Update Time Automatically

    If this has been solved before, please use the Insert Link button and reference it.

    Most likely, you just missed something. If we come up with the same solution, you may miss the crucial part as well. Typically, the crucial part is to insert the Sub in the proper object.

    Comment


    • #3
      Re: Update Time Automatically

      Russ, it looks like you didn't enter Code tags correctly. You need to place code insquare brackets before the VBA snippet & /code in square brackets at the end
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4
        Re: Update Time Automatically

        Thanks, Dave! Fantastic. Just the job.

        Please forgive my lack of experience. I thought I'd put tags round the code but I think I need to understand what that means a bit more! Back to reading the rules....

        Thanks agian.

        Russ

        Comment


        • #5
          Re: Update Time Automatically

          Russ, use the # button icon to insert VBA code tags and paste code between them. Of course you can type them as you started to in your first post.

          Here is my approach. I set it to deactivate the update when I activate some other excel workbook. Notice that I also made the cell to update to be in the workbook with the code.

          Module1 Object:
          Code:
          Option Explicit
          Public setDate As Date
          Sub UpdateTime()
            setDate = Now() + TimeValue("00:01:00")
              Application.OnTime setDate, "TimeUp"
          End Sub
          Sub TimeUp()
              ThisWorkbook.Worksheets("Sheet1").Range("H2") = Time
              UpdateTime
          End Sub
          ThisWorkBook object:
          Code:
          Option Explicit
          
          Private Sub Workbook_BeforeClose(Cancel As Boolean)
            Application.OnTime setDate, "TimeUp", , False
          End Sub
          
          Private Sub Workbook_Open()
            UpdateTime
          End Sub
          
          Private Sub Workbook_WindowActivate(ByVal Wn As Window)
            UpdateTime
          End Sub
          
          Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
            On Error Resume Next
            Application.OnTime setDate, "TimeUp", , False
          End Sub

          Comment


          • #6
            Re: Update Time Automatically

            Thanks Kenneth. Another very useful tip! I am learning more from these examples. Much appreciate the time you've taken to help me.

            Russ

            Comment

            Working...
            X