Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Need worksheet to refresh so Now() Function Calculates??

  1. #1
    New Member dhowser's Avatar
    Join Date
    Mar 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a Worksheet that calculates the time people have been gone by subtracting the time they left from a cell with the Function Now() in it. I need to keep this list current without having to press F9 or entering information. Is there a way in Excel 97 to make this work??

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Copy this code in your workbook class module.
    Will fire a recalculation every ten seconds.
    Edit as necc.
    This little snippet of code will Calculate all open workbooks.
    To change to a specific sheet or range.
    Search VBA help for Calculate.

    Have a nice day!
    Tom

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Option Explicit
    Dim I_Say_Stop

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    I_Say_Stop = True
    End Sub

    Private Sub Workbook_Open()
    Dim Start


    Start = Timer + 10
    Do Until I_Say_Stop
    Start = Timer + 10
    Do While Timer < Start And I_Say_Stop = False
    If Timer = 0 Then _
    Start = Timer + 10
    DoEvents
    Loop
    Calculate
    Loop
    End Sub


    [ This Message was edited by: TsTom on 2002-03-30 07:38 ]

  4. #4
    New Member dhowser's Avatar
    Join Date
    Mar 2002
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I inserted the text into the workbook code and it worked to begin with. I entered my info and the sheet re-calculated as expected. When I when to delete the info in a cell, I assume that it caused the timer loop to stop. What can I do to fix it??

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Sorry,
    I should have tested it.
    Are the =Now() formulas all entered in one column?
    If so, which?
    Is there anything else in that column?
    I want to place a pause in your Before_DoubleClick event.
    If a cell is double clicked the formula =Now() will be entered, if =now() is already in that cell, then it would be deleted.
    Do you understand?
    Will need to briefly disable Calculation when editing certain cells.
    I tried to trap the error but could not.
    Is generating an application defined error.
    Not a VBA error?
    I Emailed you
    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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