Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

timestamp with note change

0

Really great tutorial on YouTube about the time stamping feature! Thank you so much! However, in my spreadsheet each client has a row with various data in each cell. I have unique notes entered in all cells in column D about the respective client. How can I get the timestamp to update when I only change a note on the cell but not necessarily the cell value? Timestamp feature only activates when I change the cell value but not when I change the note in cell D. Any counsel is much appreciated.

Answer
Discuss

Answers

0

There is no application event that can capture when a comment is modified. You will find code here which simulates such an event. In the example there a comment is modified automatically, replacing the word "today" with today's date. You could adapt this code to add a date/time stamp to the comment only. Or you could modify it to trigger your existing time stamp.

If you need help doing so please post your code here. You can "Edit"{ your question and then attach a workbook to it.

Discuss
0

Hi Crash,

I hope this code works well for you

Dim s As Comment
Dim i, c As Integer
Dim act, ant As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

/// This is in order to give a inicial value to i 
    i = Application.WorksheetFunction.Max(1, i)  

    Set s = Cells(i, 3).Comment

/// In this conditional I get the value from the cell previously selected
   If s Is Nothing Then
        act = ""
    Else
        act = s.Text
    End If

/// If the note was change, write the timestamp
    If act <> ant Then Cells(i, 4) = Now

/// Only when is selected a cell in row 3, where I have the cells with notes to change
/// I get the text from note, if there is, and the row of the cell to use in the beginig of 
/// the procedure next time is run

    If Target.Column = 3 Then
        Set s = Target.Comment        

        If s Is Nothing Then
            ant = ""
        Else
            ant = s.Text
        End If
        i = Target.Row
    End If

End Sub

I also attached a sample of the file. Feel free to add, edit or delete notes in column 3 and timestamp will be in column D. Any comments or doubts, let me know

Regards
Basilio

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login