+ Reply to Thread
Results 1 to 2 of 2

I want to automatically update cell comments with time stamp last time cell changed

Hybrid View

  1. #1
    Registered User
    Join Date
    09-13-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    5

    I want to automatically update cell comments with time stamp last time cell changed

    I am trying to automatically add a comment to each cell, to show when the cell was last changed. I have the following VBA,

    Private Sub Worksheet_Change(ByVal Target As Range)
        With Target
            .ClearComments
            .AddComment ("Last Modified " & Date)
            .Comment.Visible = False
        End With
    End Sub
    which updates an individual cell's comments with "Last Modified (date)" whenever the cell content is changed. It works just fine if I enter a value into a cell, or copy and paste values of one cell to another cell. But it does not work if I change multiple cells, such as copying and pasting a range.

    How do you get this to work on a range of cells?

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: I want to automatically update cell comments with time stamp last time cell changed

    Hi, Med_MV,

    add a loop to work on each cell which has been modified:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    For Each rngCell In Target
        With rngCell
            .ClearComments
            .AddComment ("Last Modified " & Date)
            .Comment.Visible = False
        End With
    Next rngCell
    End Sub
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

+ 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. [SOLVED] Automatic update of a cell every time another one is changed
    By Romoluzzi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-04-2013, 09:52 AM
  2. Replies: 4
    Last Post: 08-21-2013, 12:41 PM
  3. [SOLVED] Extract a time from a cell with date and time stamp and then perform calculations
    By Marcos Aristotelous in forum Excel General
    Replies: 3
    Last Post: 10-31-2012, 04:36 AM
  4. Display the date and time each time the data in a cell range is changed
    By BVZM in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2012, 01:23 PM
  5. time stamp a cell that doesn,t change when time stamping another
    By RC in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-12-2005, 10:05 PM

Tags for this Thread

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