vba to reference a particular line of text in a cell's comment box

[cjcass]

Hi,
I have the code below which works fine, however how would I adjust or add to the code to change the text to bold on just the first line in the comment box, rather than all lines?
Ultimately I would want to be able to specify the line number, eg. line 1, line 5 etc..
Any help much appreciated.
Thanks.

Sheet12.Select
    Range("D9").Comment.Text Text:="Feedback:" & Chr(10) & Range("F4") & Chr(10) & Range("F5")
    Range("D9").Comment.Shape.TextFrame.Characters.Font.Size = 10
    Range("D9").Comment.Shape.TextFrame.Characters.Font.Bold = True
    Range("D9").Comment.Shape.TextFrame.AutoSize = True

 

[Darren Bartrup]

You need to look at the correct characters:

Range("D9").Comment.Shape.TextFrame.Characters(1, 9).Font.Bold = True

1 is the start, 9 is the length - maybe use INSTR to find the first Chr(10) character.

 

[cjcass]

Hi,
Thanks for responding to this.
I understand what you are saying but the number pf characters in the Ranges F4 and F5 (for example) will be variable.

 

[Darren Bartrup]

This might work better, although it's not perfect.
Pass the EditComment procedure a single range, the comment and optionally which line you want making bold.

Sub Test()
    EditComment Sheet1.Range("A9"), "Feedback:" & Chr(10) & Range("F4") & Chr(10) & Range("F5"), 2
End Sub
Public Sub EditComment(Target As Range, CommentText As String, Optional BoldLine As Long)
    Dim NewLines As Long
    Dim CommentLines As Long
    Dim Start As Long, Finish As Long
    Dim TextLines As Variant
    Dim y As Long
    With Target
        If .Cells.Count = 1 Then
            NewLines = Len(CommentText) - Len(Replace(CommentText, Chr(10), ""))
            
            'Add a comment if it doesn't exist, otherwise add text to existing comment.
            If .Comment Is Nothing Then
                CommentLines = 0
                .AddComment CommentText
            Else
                CommentLines = Len(.Comment.Text) - Len(Replace(.Comment.Text, Chr(10), ""))
                .Comment.Text Text:=.Comment.Text & Chr(10) & CommentText
            End If
            
            If BoldLine > 0 Then
                'Find the specified line number and make it bold.
                'Problem with this is that it will remove any existing formatting in the comment.
                TextLines = Split(.Comment.Text, Chr(10))
                For y = 0 To CommentLines + BoldLine - 1
                    Start = Start + Len(TextLines(y)) + 1
                Next y
                Finish = Start + Len(TextLines(y))
                .Comment.Shape.TextFrame.Characters(Start + 1, Finish - Start).Font.Bold = True
            End If
        End If
    End With
End Sub

 

[cjcass]

Hi,

Thanks for coming back and for your time.

A couple of things:

1) I wanted the first line 'Feedback:' to be in bold so I changed the last number in the Sub Test () Edit Comment line from '2' to '1' but it made the second line of text from Range("F4") bold instead. I then changed it to '0' and then no lines were bold?
2) Also I would always want the comment to override any existing comment, rather than add to it, can this be achieved?
3) Lastly, the number of cells with text being put into the comment box may vary, so I may also need to add, for example; & Chr(10) & Range("F6") & Chr(10) & Range("F7") - would the code be able to handle these additions?

Thanks again for your help.

 

[Darren Bartrup]

Sorry, didn't test it properly.

Having a new comment each time makes life easier. Code below will delete an existing comment and add a new. It will then turn the first line bold.
If your first line is always 'Feedback:' then my first answer will work - it just turns the first 9 characters bold, anything after that isn't - the length of text in F4 & F5 doesn't matter.

Sub Test()
    AddComment Sheet2.Range("A9"), "Extra Feedback:" & Chr(10) & Range("F4") & Chr(10) & Range("F5")
End Sub
Public Sub AddComment(Target As Range, CommentText As String)
    With Target
        If .Cells.Count = 1 Then
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            .AddComment CommentText
            .Comment.Shape.TextFrame.Characters(1, InStr(CommentText, Chr(10))).Font.Bold = True
        End If
    End With
End Sub

 

[cjcass]

That works brilliantly, thanks very much

Sorry to add onto the original requirement, but I have one last question if I may:

If I additionally wanted to make the second line in the comment box have a red font could I just make a change to this line in some way to refer to the second line (the text from Range F4)...

.Comment.Shape.TextFrame.Characters(1, InStr(CommentText, Chr(10))).Font.Bold = True

Many thanks.