VBA Code has bug

Z

zulfer7

I have this code in VBA in an Excel Spreadsheet but it has a bug. I cannot
tell if this happens all the time but this code seems to copy the complete
contents of the comment at times when it is only intended to add to the
comment. Any help please!!

Example (1st line of code)
Excel User 05/01/2006

Example (When want to add to the code at a later date)
Excel User 05/01/2006
Excel User 05/01/2006 (This line is repeated)
New User 05/02/2006

Example (When want to add yet another comment later)
Excel User 05/01/2006
Excel User 05/01/2006 (This line is repeated)
New User 05/02/2006
Excel User 05/01/2006 (This line is repeated)
Excel User 05/01/2006 (This line is repeated)
New User 05/02/2006 (This line is repeated)
Another User 05/03/2006

Sub KeyCellsChanged()
Dim strDate As String
Dim cmt As Comment
Dim Username As String


strDate = "ddmmmyy hh:mm"
Username = Application.Username
Set cmt = ActiveCell.Comment

If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=Username & Format(Now, strDate) & Chr(10)

Else
cmt.Text Text:=cmt.Text & Chr(10) _
& Username & Format(Now, strDate) & Chr(10)

End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top