CODEWITHSUNDEEP
vbaexcel
Ang3lStyl3X
Ang3lStyl3X

Reputation: 63

Excel VBA comments update

I'm trying to make code that check if comment exists and then update it(just append the new text to end of the comment), I don't want delete the comment data. My new text for the comment taken from the cell value. My code always delete the old comment and add new one..

                            comm = user & ": " & ActiveCell.Value
                            With ActiveCell
                            If ActiveCell.Column = 10 Then
                                ActiveCell.Interior.ColorIndex = 27
                            Else
                                ActiveCell.Interior.ColorIndex = 0
                            End If
                            With ActiveCell.Borders
                                .LineStyle = xlContinuous 'Setting style of border line
                                .Weight = xlThin 'Setting weight of border line
                                .ColorIndex = xlAutomatic 'Setting colour of border line
                            End With
                            If .Comment Is Nothing Then
                                .AddComment
                                .Comment.Text Text:=comm
                                .Comment.Shape.TextFrame.AutoSize = True
                            Else
                                comm = .Comment.Text
                                Txt = comm & vbNewLine & Txt
                                .Comment.Text Text:=Txt
                                .Comment.Shape.TextFrame.AutoSize = True
                            End If
                        End With

Upvotes: 0

Views: 2591

Answers (2)

Walerij Bogdanow
Walerij Bogdanow

Reputation: 21

There is no need to pre-save the existing comment text in order to add some text to it. Note: the description on https://learn.microsoft.com/en-us/office/vba/api/excel.comment.text is not correct.

Just try this code, and you unterstand, how it works indeed.

With Range("A10")
    If .Comment Is Nothing Then .AddComment
    .Comment.Shape.TextFrame.AutoSize = True
    'the first argument - the text to be added to the note
    'the second argument - starting from which sign (cannot be zero)
    'the third argument is how many characters to overwrite (can be zero)

    'Insert the text at the beginning (i.e. starting from the 1st character and overwriting 0 characters)
    .Comment.Text Format(Now, "dd.MM.yyyy hh:mm:ss") & " Some text" & vbNewLine, 1, 0
    'Insert the text at the end (i.e. starting at 10000 characters and overwriting 0 characters). The number 10000 is taken as an example of any maximal number above which the comment is never supposed to be
    .Comment.Text Format(Now, "dd.MM.yyyy hh:mm:ss") & " Some text" & vbNewLine, 10000, 0
End With

Upvotes: 1

AJD
AJD

Reputation: 2438

No need to make this so complicated. Note that Comment.Text is a method, not a property.

If .Comment Is Nothing Then
    .AddComment comm
Else
    .Comment.Text comm & vbNewLine, 1, False ' according to MSDN this will pre-pend the text, not overwrite - tested and works
    .Comment.Text .Comment.Text & vbNewLine & comm ' Alternative approach, this should overwrite with the new combined text
End If
.Comment.Shape.TextFrame.AutoSize = True ' because we know a comment exists after running this code

Upvotes: 2

Related Questions