Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Update Time Automatically

  1. #1
    Join Date
    12th April 2007
    Posts
    3

    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:

    VB:
    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 at 21:54.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,092

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,031

    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

    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.

  4. #4
    Join Date
    12th April 2007
    Posts
    3

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,092

    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:
    VB:
    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:
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th April 2007
    Posts
    3

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: May 30th, 2008, 00:11
  2. Update Links Automatically
    By cdu311 in forum EXCEL HELP
    Replies: 4
    Last Post: April 25th, 2007, 17:10
  3. Automatically Update Workbook
    By Gem in forum EXCEL HELP
    Replies: 5
    Last Post: March 27th, 2006, 22:25
  4. Update link automatically
    By subash in forum EXCEL HELP
    Replies: 3
    Last Post: September 11th, 2005, 08:51
  5. Automatically update cells
    By gstettner in forum EXCEL HELP
    Replies: 2
    Last Post: July 25th, 2003, 22:45

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno