Protected Worksheet BeforeDoubleClick Event Cancel=True Allowing Cell Contents to be Edited

[jhedges]

I have the following code in place to put in a comment when you double-click in the cell. The code seems to work fine; however, when I then click in the cell (after inserting a comment) and then up in the formula bar, the text goes away leaving the comment. Wondering if anyone can tell me why the text goes away with just these clicks...I haven't hit any other keys? I would like to add the comment, but not give the user the ability to delete the text in the cells. If I hit undo, then the text comes back. I thought cancel=true was supposed to prevent this?

'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
    
    Sheets("Assignment Schedule").UnProtect Password:="XXX"
    
    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
    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
        'Target.Comment.text Target.Comment.text & Chr(10) & cmtText
    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 = 1
    Target.Comment.Shape.TextFrame.Characters(StartPos + 21, NameLength).Font.ColorIndex = 3
    Target.Comment.Shape.TextFrame.Characters(StartPos + 21 + StaffLength + 1 + NameLength + 1).Font.ColorIndex = 46
    Target.Comment.Shape.TextFrame.Characters(StartPos + 21 + StaffLength + 1 + NameLength + 1, GoalLength + 1).Font.ColorIndex = 32
    
    Sheets("Assignment Schedule").Protect Password:="XXX"
    Cancel = True    'Remove this if you want to enter text in the cell after you add the comment
    
End Sub

 

[Hjemmet]

I do not have much sense on VBA code but after reviewing and testing your VBA code I can see missing a reference where the text from the message box is written
to the cell or cells that double click on

Your regards from Hjemmet

 

[jhedges]

The code puts in the comment as well as pulls text from the worksheet and the date/time into the comment. All this is working fine and it leaves the cell contents in tact. ​

After running the code and then clicking in the formula bar the text in the cell goes away, leaving the comment. Wondering why the text in the cell goes away?

 

[Hjemmet]

It's probably about the protection of the sheet there gives the Trouble.