ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   VBA Code has bug (https://www.excelbanter.com/excel-discussion-misc-queries/99982-vba-code-has-bug.html)

zulfer7

VBA Code has bug
 
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


All times are GMT +1. The time now is 02:32 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com