Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
In an excel file I have a list of names and want people to enter comments for the latest status. Is there a way for excel to calculate that date the comment field was last updated each time? So today is 11/20 but the last comment was posted 11/01, I'd like the date to say 11/01 automatically...
I would default all the dates to today's date then would like them to change as people update their statuses.
The following was written by Julian Milano many years ago. I pass it along, hopefully with his permission.
Before I do, I'll add this:
Run this code to disable the user's ability to edit or delete comments via Right-Click. This will help them remember to use the custom button that Julian suggests.
With Application.CommandBars("Cell")
.Controls("Delete Comment").Enabled = True 'True to reset
.Controls("Edit Comment").Enabled = True
End With*** Begin Included Text ***
The following will add a comment to a cell and place the user's name, time
of comment & date. If a comment exists, the new one will be appended to the
end. You could set this up as a button on a toolbar:,
Sub CustomComments()
' Set up the Error Handler. This should only trigger
' when we try to add a comment to an existing comment
On Error GoTo ChkError
Selection.AddComment
Selection.Comment.Visible = False
AddNewComment ' The Error Handler didn't trigger,
' so add a new comment
Done: Exit Sub ' STOP- Do not go further.ChkError: ' ERROR! A comment exists
If Err.Number = 1004 Then ' 1004 is the error that comes up
AddToComment ' Add to the existing comment
End If
Err.Clear ' Now clear the error
GoTo Done ' Finish off the macro
End SubSub AddToComment()
OldCommentText = Selection.Comment.Text
NewCommentText = InputBox("The current Comment text is: " & _
Chr(10) & OldCommentText & Chr(10) & Chr(10) & "Enter new text: ")
Selection.Comment.Text Text:=OldCommentText & Chr(10) & _
Application.UserName & Chr(10) & Now() & Chr(10) & NewCommentText
End SubSub AddNewComment()
NewCommentText = InputBox("Enter the new Comment text: " & _
Chr(10))
Selection.Comment.Text Text:=Application.UserName & Chr(10) & _
Now() & Chr(10) & NewCommentText
End Sub
***End Included Text ***
0 
 |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
