Comment Automation

[default_name]

I have set up a weekly log of my work, etc.

Each day I enter in the amount of time spent on certain tasks.
When I enter a number, I am prompted to write a brief explanation of the specific work I did.
The explanations are stored as comments, which appear as I hover over them.
Awesome! This is what I want it to do.

There is, however, something I am trying to change.
If I change the number of hours I am re-promted for a new explanation, and the previous explanation/comment is overwritten.
It is somewhat inconvenient when I have to copy my current comment/explanation (which are sometimes fairly long and detailed) so that I can then change the contents of the cell (update the time slightly), and then paste the previous comment/explanation again.

In other words, the cell content is changing (the amount of time spent) but I want the comment to remain as it was.
If the cell already has content, is there a way to alter the cell, while keeping the current comment (disabling the prompt that asks for a new comment/explanation)?

Here is the code I am currently using to make this happen (I have this message board to thank for helping me with this original code):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, c As Range, Cmnt As String
Set R = Range("G3:K15,G21:K33")
If Not Intersect(Target, R) Is Nothing Then
    Application.EnableEvents = False
    For Each c In Intersect(Target, R)
        If c.Value = "" Then c.ClearComments
Again:      If LCase(c.Value) > "0" Then
            If c.Comment Is Nothing Then
                Cmnt = InputBox("What did you do for the amount of time you entered?", "Brief Explanation of Labor")
                If Cmnt = "" Then
                    Application.EnableEvents = True
                    Exit Sub
                End If
                c.AddComment
                c.Comment.Text Text:=Cmnt
            Else
                c.ClearComments
                GoTo Again
            End If
        End If
    Next c
End If
Application.EnableEvents = True
End Sub

Also, is there any way that I can auto-format the size of the comment box to fit however much text is in the comment?
I normally have to manually go in and edit the size of the comment box in order to see everything for long or detailed comments.

Hopefully this makes sense.
Thanks in advance for your help!

 

[John_w]

See if you can incorporate the Append_Comment code into your routine:

Sub test()
    Append_Comment Range("B10"), "First"
    Append_Comment Range("B10"), "Second"
End Sub

Public Sub Append_Comment(cell As Range, commentText As String)
    
    Dim TF As TextFrame
    
    If cell.Comment Is Nothing Then
        cell.AddComment
        cell.Comment.text commentText
    Else
        Set TF = cell.Comment.Shape.TextFrame
        TF.Characters(TF.Characters.Count + 1).Insert vbLf
        TF.Characters(TF.Characters.Count + 1).Insert commentText
    End If
    
    cell.Comment.Shape.TextFrame.AutoSize = True

End Sub

The vbLf puts the comments on separate lines.

 

[My Aswer Is This]

Try This:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/10/2018  9:10:26 PM  EDT
If Not Intersect(Target, Range("G3:K15,G21:K33")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
    If Target.Value > 1 Then
        If Target.Comment Is Nothing Then
            ans = InputBox("What did you do for the amount of time you entered?", "Brief Explanation of Labor")
            Target.AddComment: Target.Comment.Text Text:=ans
            Target.Comment.Shape.TextFrame.AutoSize = True
        End If
    End If
End If
End Sub

 

[My Aswer Is This]

If you want the comments to be hidden until you hover over the cell then you will need this script.
I added one more line of code to the script.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/10/2018  9:22:16 PM  EDT
If Not Intersect(Target, Range("G3:K15,G21:K33")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
    If Target.Value > 1 Then
        If Target.Comment Is Nothing Then
            ans = InputBox("What did you do for the amount of time you entered?", "Brief Explanation of Labor")
            Target.AddComment: Target.Comment.Text Text:=ans
            Target.Comment.Shape.TextFrame.AutoSize = True
            Target.Comment.Visible = False
            
            
        End If
    End If
End If
End Sub

 

[default_name]

Thanks for your guys' help!
@John_w: When I implement your example I cannot get it to work. :/
@My Aswer Is This: This code is on the right track for what I'm looking for, but is there a way to clear the comment if the cell is emptied?
I like that the comment remains even if I alter the contents of the cell to another value...but in the instance that I need to completely clear the cell is there a way to also clear the comment?

Thanks again for all your help!

 

[John_w]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim inputCells As Range, cell As Range, commentText As String
    
    Set inputCells = Range("G3:K15,G21:K33")
    
    If Not Intersect(Target, inputCells) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Intersect(Target, inputCells)
            If cell.Value = "" Then
                cell.ClearComments
            ElseIf Val(cell.Value) > 0 Then
                commentText = InputBox(cell.Address(False, False) & ": What did you do for the amount of time you entered?", "Brief Explanation of Labor")
                If commentText <> "" Then
                    If cell.Comment Is Nothing Then
                        cell.AddComment commentText
                    Else
                        cell.Comment.text cell.Comment.text & vbLf & commentText    'append new comment
                        'cell.Comment.text commentText & vbLf & cell.Comment.text    'prepend new comment
                    End If
                    cell.Comment.Shape.TextFrame.AutoSize = True
                End If
            Else
                cell.ClearComments
            End If
        Next
        Application.EnableEvents = True
    End If
    
End Sub

 

[default_name]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim inputCells As Range, cell As Range, commentText As String
    
    Set inputCells = Range("G3:K15,G21:K33")
    
    If Not Intersect(Target, inputCells) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Intersect(Target, inputCells)
            If cell.Value = "" Then
                cell.ClearComments
            ElseIf Val(cell.Value) > 0 Then
                commentText = InputBox(cell.Address(False, False) & ": What did you do for the amount of time you entered?", "Brief Explanation of Labor")
                If commentText <> "" Then
                    If cell.Comment Is Nothing Then
                        cell.AddComment commentText
                    Else
                        cell.Comment.text cell.Comment.text & vbLf & commentText    'append new comment
                        'cell.Comment.text commentText & vbLf & cell.Comment.text    'prepend new comment
                    End If
                    cell.Comment.Shape.TextFrame.AutoSize = True
                End If
            Else
                cell.ClearComments
            End If
        Next
        Application.EnableEvents = True
    End If
    
End Sub

Thanks, but that code works almost exactly like the code I previously had (see initial post).
It does give me the added comment auto-sizing, though.
One step closer.

I just want to be able to modify the value in the cell without being re-prompted for a new comment every time I do so.
I want the comment to stay with the cell, unless the value in the cell is cleared.

Hope that makes sense.

Thanks for your persistence and help with this

 

[John_w]

Sorry, misread your OP: I thought you wanted to append the new comment to the existing comment!

Try this (I've removed Application.EnableEvents True/False, because I don't think it's needed - the code doesn't change the sheet values).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim inputCells As Range, cell As Range, commentText As String
    
    Set inputCells = Range("G3:K15,G21:K33")
    
    If Not Intersect(Target, inputCells) Is Nothing Then
        For Each cell In Intersect(Target, inputCells)
            If cell.Value = "" Then
                cell.ClearComments
            ElseIf cell.Comment Is Nothing Then
                commentText = InputBox(cell.Address(False, False) & ": What did you do for the amount of time you entered?", "Brief Explanation of Labor")
                If commentText <> "" Then
                    cell.AddComment commentText
                    cell.Comment.Shape.TextFrame.AutoSize = True
                End If
            End If
        Next
    End If
    
End Sub

 

[default_name]

Sorry, misread your OP: I thought you wanted to append the new comment to the existing comment!

Try this (I've removed Application.EnableEvents True/False, because I don't think it's needed - the code doesn't change the sheet values).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim inputCells As Range, cell As Range, commentText As String
    
    Set inputCells = Range("G3:K15,G21:K33")
    
    If Not Intersect(Target, inputCells) Is Nothing Then
        For Each cell In Intersect(Target, inputCells)
            If cell.Value = "" Then
                cell.ClearComments
            ElseIf cell.Comment Is Nothing Then
                commentText = InputBox(cell.Address(False, False) & ": What did you do for the amount of time you entered?", "Brief Explanation of Labor")
                If commentText <> "" Then
                    cell.AddComment commentText
                    cell.Comment.Shape.TextFrame.AutoSize = True
                End If
            End If
        Next
    End If
    
End Sub

That works PERFECTLY! Thank you so much!

 

[My Aswer Is This]

I would try this script and see what you think.
I did not realize you wanted comments deleted if you cleared cell.
This script also hides your comments and only shows them when you hover over cell which I thought was what you wanted.

The auto size I used I think works better. It sizes the comment better in my opinion if you have a large comment.

I also have it color the comment. You can change color if you want. Or remove this line of code if you like default color.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/11/2018  5:05:42 PM  EDT
If Not Intersect(Target, Range("G3:K15,G21:K33")) Is Nothing Then
If Target.Cells.CountLarge > 1 Then Exit Sub
Dim ans As String
Dim lArea As Long
    If Target.Value = "" Then Target.ClearComments: Exit Sub
    
    If Target.Value > 1 Then
        If Target.Comment Is Nothing Then
            ans = InputBox("What did you do for the amount of time you entered?", "Brief Explanation of Labor")
            Target.AddComment: Target.Comment.Text Text:=ans
            
            With Target.Comment.Shape
                .TextFrame.AutoSize = True
                .Fill.ForeColor.SchemeColor = 7  'Change color here if you want different color
                    If .Width > 300 Then
                        lArea = .Width * .Height
                        .Width = 200
                        .Height = (lArea / 200) * 1.2
                    End If
            End With
            Target.Comment.Visible = False
            
            
        End If
    End If
End If
End Sub