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 for comments excel 365

0

Hi,

  1. I'm Tom and new on this forum. I searched for some days and don't found the solution for my problem.

I have a table with different data, and in column "W" i want to put comments to the actual situation, and for example one week later add another comment etc. Depending on the row, I have sometimes 1 comment, sometimes 20+. I want  to filter this table by the date the comment was added, or create a filtr for the last 5, 10, 20 working days. 

Is it possible to write a VBA code to input a timestamp in a cell after adding or edditing a comment in excel 365?

Or another way to arrange the table according to the comment date? Maybe a way to exteact the date from the comment?

Any help welcome :)

EDIT

Yes, it means Threaded Comments in Excel 365, with Replies.

If they would be separate comments, I think the solution that I have for all the other data should work (I'm using this to see the last changed row)

I use the Notes feature for pictures & attachements, like in the file.

Maybe there is a better way to do this? I mean both things ;)

Answer
Discuss

Discussion

Hi Tom and welcome to the Forum.

When you say you may have 1 or 20 comments, do you mean in a single cell in column W (using Threaded Comments in Excel 365, with Replies) or are they separate comments? Or do you use the Notes feature?

It nearly always helps us if you attach an Excel file to your question (but don't write the question in there please, just point us to key cells in your Question text).

Please edit your question and use the Add Files... button to upload a representative Excel file (without any personal data) to show any existing macro and data. Then we should be able to give specific help.
John_Ru (rep: 6102) Jan 7, '23 at 5:39 pm
Thanks for explaining / attaching a file. I'll check and reply tomorrow (unless someone else does so before) 
John_Ru (rep: 6102) Jan 8, '23 at 9:10 am
Hi again Tom. Did you try my Answer/ file? You will need to add code to it but I'm surprised to get no response from you.
John_Ru (rep: 6102) Jan 10, '23 at 7:57 am
Add to Discussion

Answers

0

Tom

As an alternative to putting a date in column W (which you seem to have a macro for- if not, please use the method in Don's tutorial here: Automatically Timestamp Entries in Excel ), in the attached file I've added the macro below which will print any comments in a date range into VB Project Explorer's Immediate Window: 

Private Sub FilterComments()

Dim wComm As CommentThreaded, Sep As String
Dim StrtDt As Date, EndDt As Date, n As Large

' get/ test start date from user
StrtDt = InputBox("Enter earliest date to filter comments(and replies) in column W from:", "Filter date(1)", Date - 7)
If Not IsDate(StrtDt) Or StrtDt > Date Then MsgBox "Try again with a proper start date": Exit Sub

' get/ test start date from user (default-today)
EndDt = InputBox("Enter the last date to filter on:", "Filter date(1)", Date)
If Not IsDate(EndDt) Or EndDt < StrtDt Then MsgBox "Try again with a proper end date": Exit Sub
' define a text separator
Sep = "; "

' run through comments
For Each wComm In ActiveSheet.CommentsThreaded
    With wComm
        ' only check if in column W
        If InStr(.Parent.Address, "$W$") = 1 Then
        ' if in column W, give address and details
        Debug.Print .Parent.Address & Sep & .Date & Sep & .Author.Name & Sep & .Text
            ' loop through any replies...
            If .Replies.Count > 0 Then
                For n = 1 To .Replies.Count
                  With .Replies(n)
                    ' ... checking that are in the user date range
                    If .Date >= StrtDt And .Date < EndDt + 1 Then
                    '... is so, print out (or do something else)
                        Debug.Print "Reply " & n & Sep & .Date & Sep & .Author.Name & Sep & .Text
                    End If
                  End With
                Next n
            End If
        End If
    End With
Next wComm


End Sub

Run it from VB Project Explorer and you should get results like this (from your file) using the (current) default Start / End dates of 02 Jan and today (where first item in bold below is the cell address... 

$W$5; 08/01/2023 11:23:50; Tom*** ******; Here I want add comments
Reply 1; 08/01/2023 11:24:14; Tom*** ******; And then add another comment
Reply 2; 08/01/2023 11:24:54; Tom*** ******; And I want in column W the date when the comment was added

... and the *** ****** portions have been added, just to redact some personal info)

You can change the dates (e.g. to get only items from 08 Jan).

Where I've got the first bold Debug.Print line above:

Debug.Print .Parent.Address & Sep & .Date & Sep & .Author.Name & Sep & .Text

you could change that to write cells from columns A to W of that row to another worksheet and concatenate the replies to the new W cell so you have a filtered results page.

Hope this helps (if so, please remember to mark this Answer as Selected). If you need to know how to add a results sheet and output items, name it etc., kindly ask a new question.

Discuss


Answer the Question

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