Insert Cell Comment with a VBA Before_DoubleClick event Question

jhedges

Board Regular
Joined
May 27, 2009
Messages
208
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?

Code:
'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.
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try replacing this:

Code:
Target.NoteText Chr(10) & cmtText, 99999

with this:

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

WBD
 
Upvote 0
Try replacing this:

Code:
Target.NoteText Chr(10) & cmtText, 99999

with this:

Code:
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?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top