+ Reply to Thread
Results 1 to 6 of 6

What is wrong with my Worksheet_Calculate timestamp code?

  1. #1
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    What is wrong with my Worksheet_Calculate timestamp code?

    Hi all,

    I have been trying to write a code that will display the NOW (date&time) in an adjacent cell if the target cell value changes.

    The target cell is changed by a formula and references values from another sheet, so Worksheet_Change and circular argument functions will not work.

    Therefore, I found a possible solution, which you can see below - it worked once or twice, and now the timestamp will not update. I need the adjacent cell's timestamp to update every time the target cell value changes.

    Option Explicit
    Private Sub Worksheet_Calculate()
    Dim CELL As Range

    Application.EnableEvents = False
    For Each CELL In Range("B13")
    If CELL.Value <> CELL.Offset(, 1).Value Then
    With CELL.Offset(1, -1)
    .Value = Now
    .NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
    End With
    CELL.Offset(1, -1).Value = Now
    End If
    Next CELL
    Application.EnableEvents = True
    End Sub
    I am new to VBA so I am hoping somebody can take a look and tell me where I am making a mistake in my code.

    Thank you all so much for your help.

    -A

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: What is wrong with my Worksheet_Calculate timestamp code?

    Hi arwebb,

    I think you have your events hung on Calculate instead of Change. See if this code does what you want. Note: I hate Offset so do code using Cell(row,column) instead.

    Please Login or Register  to view this content.
    Also - put the above code behind the worksheet and not in a Module.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: What is wrong with my Worksheet_Calculate timestamp code?

    Hi Marvin!

    Thank you so much for taking a look. I agree with your take on avoiding the Offset setup - it was confusing to me.

    I entered your code and unfortunately had no result - meaning that no timestamp appeared anywhere in sheet.

    Are you sure that I can do a Worksheet_Change for this timestamp, considering that the target cell's value is changed by a formula reference from another worksheet? I thought I read that I'd need a Worksheet_Calculate script for that.

    Any advice would be so helpful!

  4. #4
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: What is wrong with my Worksheet_Calculate timestamp code?

    Hi Marvin!

    Thank you so much for taking a look. I agree with your take on avoiding the Offset setup - it was confusing to me.

    I entered your code and unfortunately had no result - meaning that no timestamp appeared anywhere in sheet.

    Are you sure that I can do a Worksheet_Change for this timestamp, considering that the target cell's value is changed by a formula reference from another worksheet? I thought I read that I'd need a Worksheet_Calculate script for that.

    Any advice would be so helpful!

  5. #5
    Registered User
    Join Date
    02-10-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    65

    Re: What is wrong with my Worksheet_Calculate timestamp code?

    Quote Originally Posted by MarvinP View Post
    Hi arwebb,

    I think you have your events hung on Calculate instead of Change. See if this code does what you want. Note: I hate Offset so do code using Cell(row,column) instead.

    Please Login or Register  to view this content.
    Also - put the above code behind the worksheet and not in a Module.
    I entered your code and unfortunately had no result - meaning that no timestamp appeared anywhere in sheet.

    Are you sure that I can do a Worksheet_Change for this timestamp, considering that the target cell's value is changed by a formula reference from another worksheet? I thought I read that I'd need a Worksheet_Calculate script for that.

    Any advice would be so helpful!

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: What is wrong with my Worksheet_Calculate timestamp code?

    The Change event fires when a cell is changed manually or via code, not via formula.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how do I allow for 24:00 (2400) timestamp in my code ??
    By Dj Duck in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-27-2016, 08:24 PM
  2. VBA Code for Automatic Timestamp
    By Mhace in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-18-2015, 02:02 AM
  3. Convert code to a Worksheet_Calculate version?
    By maistral in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-31-2013, 12:30 PM
  4. Problem with timestamp code
    By Rtoro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2007, 03:12 AM
  5. static timestamp that updates using vba code
    By xenador in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-27-2007, 02:56 AM
  6. Popup 40 days after timestamp using vba code
    By xenador in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-26-2007, 02:36 AM
  7. Code for a timestamp.
    By Zirus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-15-2007, 01:10 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1