Insert Cell Comment with a VBA Before_DoubleClick event Question

[jhedges]

I have adopted the following code from this site I believe; however, not sure who to give credit to at the moment. My problem is when I already have a cell with a comment inserted by this macro and want to add an additional comment to the same cell it doesn't work correctly in my opinion. If I double click the cell and add a sentence in as my comment into the inputbox it simply goes away & the code doesn't put the comment in. If I type something small like the letter "C" into the InputBox it adds the "C" in with the other data added by the code into a comment. I can then edit the comment from Excel and add in what I need. So, initially I can type what I want and it proceeds to enter the comment. Adding any additional comments via the code has to be a short entry, which I can then edit. Wondering if anyone know why this is happening?

'Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    On Error Resume Next
    Dim cmtText As String
    Dim staffText As String
    Dim goalText As String
    Dim Pos As Long
    Dim i As Integer
    Dim text As String
    Dim lArea As Long
    Dim TotalCommentLength As String
    Dim cmtTextLength As Long
    Dim NameLength As Long
    Dim GoalLength As Long
    Dim StaffLength As Long
    Dim StartPos As Long
    Dim ThePW As String
    ThePW = InputBox("A password is required to run this procedure." & vbCrLf & "please enter the password:", "Password")
    If ThePW <> "xxxx" Then Exit Sub
    cmtText = InputBox("Please Enter 90 Day Plan(s):", "Goal Text")
    staffText = ActiveCell.Offset(0, -3).Value
    goalText = ActiveCell.Offset(0, 0).Value
    If cmtText = "" Then Exit Sub
    UnProtect
    NameLength = Len(Application.UserName)
    GoalLength = Len(goalText)
    StaffLength = Len(staffText)
    'include line feed at end of text to prevent format bleeding
    cmtText = Format(Now, "mm/dd/yy hh:mm:ss ampm") & "-" & Application.UserName & "-" & staffText & " " & goalText & " " & vbCrLf & "STAFF 90day Plan:" & " " & cmtText & Chr(10)
    cmtTextLength = Len(cmtText)
    If Target.Comment Is Nothing Then
        Target.AddComment text:=cmtText
    Else
        Target.NoteText Chr(10) & cmtText, 99999
    End If
    'Auto size the comment area
    With Target.Comment.Shape
        .TextFrame.AutoSize = True
        If .Width > 350 Then
            lArea = .Width * .Height
            .TextFrame.AutoSize = False
            .Width = 350
            ' An adjustment factor of 0.8 seems to work ok.
            .Height = (lArea / 350) * 0.9
        End If
    End With
    'color the date and name text
    
    TotalCommentLength = Len(Target.Comment.text)
    StartPos = TotalCommentLength - cmtTextLength + 1
    Target.Comment.Shape.TextFrame.Characters(StartPos, 20).Font.ColorIndex = 41
    Target.Comment.Shape.TextFrame.Characters(StartPos + 21, NameLength).Font.ColorIndex = 3
    Target.Comment.Shape.TextFrame.Characters(StartPos + 21 + StaffLength + 1 + NameLength + 1, GoalLength).Font.ColorIndex = 29
    Target.Comment.Shape.TextFrame.Characters(StartPos + 21 + StaffLength + 1 + NameLength + 1, GoalLength + 1).Font.ColorIndex = 32
    Cancel = True    'Remove this if you want to enter text in the cell after you add the comment
End Sub

Sincerely and thanks for your help.

 

[wideboydixon]

Try replacing this:

Target.NoteText Chr(10) & cmtText, 99999

with this:

Target.Comment.Text Target.Comment.Text & Chr(10) & cmtText

WBD

 

[jhedges]

Try replacing this:

Target.NoteText Chr(10) & cmtText, 99999

with this:

Target.Comment.Text Target.Comment.Text & Chr(10) & cmtText

WBD

This works; however, I loose the formatting of the comments I have specific coloring based upon what is being put into the comment. After replacing the code referenced above the comment already located in the cell gets colored a light blue in its entirety. The new comment has elements colored correctly and others not. I think I can adjust the coloring with the new comment; however, I'm not sure why the comment(s) already in the cell do not keep the color formatting as when they were inserted?