Unlocking just the added comment for editing

jhedges

Board Regular
Joined
May 27, 2009
Messages
208
I have found the following code on this site, which works great. What I would like to do is unlock just the comment to allow the user to go to "Review" then "Edit Comment" if needed.

Code:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
ActiveSheet.Unprotect Password:="xxxxx"
    On Error Resume Next
    Dim cmtText 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 StartPos As Long
    cmtText = InputBox("Please enter Comment:", "Comment Text")
    If cmtText = "" Then Exit Sub
    NameLength = Len(Application.UserName)
    'include line feed at end of text to prevent formatt bleeding
    cmtText = Format(Now, "mm/dd/yy hh:mm:ss ampm") & "-" & Application.UserName & "  " & cmtText & Chr(10)
    cmtTextLength = Len(cmtText)
    If Target.Comment Is Nothing Then
        Target.AddComment text:=cmtText
        'Target.Comment.Visible = True
    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.8
        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
    'some of your code follows
    Dim Col As Integer, Row As Integer
    Row = Target.Row
    Col = Target.Column
    ' 'Call function to format cell
    If Not Target Is Nothing Then
        'FormatCellTemplateSheet Row, Col
    End If
    Cancel = True    'Remove this if you want to enter text in the cell after you add the comment
ActiveSheet.Protect Password:="xxxxx"
End Sub

I was thinking about adding something like the follwing, just didn't know where to add it...I also need to make the comment fill non transparent...

Code:
    With Selection
        .Locked = False
        .LockedText = False
    End With
 
Last edited:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,216,084
Messages
6,128,724
Members
449,465
Latest member
TAKLAM

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