VBA fully operates the code for the Excel cell comments

First, get the comments for the cell

Private Subcommandbutton1_click ()

Dim Strgotit as String

Strgotit

= Worksheetfunction.clean (Range ("A1"). Comment.text)

MsgBox Strgotit

End Sub

Range.Comment.Text is used to get the memo text for a cell, and if the current cell does not add a comment, an exception is thrown. Note that the WorksheetFunction object is used in the code, which is an Excel system object that provides a number of system functions, where the clean function is used to clearly specify all the keywords in the text (special characters), and the specific information can be found in Excel's own Help document. There are a lot of functions available inside. The following is an example of using the Application.WorksheetFunction.Substitute function, where the first substitute the author in the given string: Replace with an empty string, The second substitute replaces the space in the given string with an empty string.

Private Functioncleancomment (author As String, CMT as String) as String

Dim tmp as String

TMP = Application.WorksheetFunction.Substitute (CMT, Author & ":", "")

TMP = Application.WorksheetFunction.Substitute (tmp, CHR (10), "")

Cleancomment = tmp

End Function

Automatically add comments information to cells when you modify the contents of Excel cells

Private Subworksheet_change (ByVal Target as Excel.Range)

Dim NewText as String

Dim OldText as String

For each cell in Target

With cell

On Error Resume Next

OldText =. Comment.text

If Err <> 0 Then. AddComment

NewText = OldText & "Changed by" & Application.UserName & ' at ' & Now & VBLF

Msgboxnewtext

. Comment.text NewText

. Comment.visible = True

. Comment.Shape.Select

Selection.autosize = True

. Comment.visible = False

End With

Next cell

End Sub

The comments content can be modified as needed, and the Worksheet_change method executes when the contents of the worksheet cell are modified.

Iii. change the display status of the comment label

Subtogglecomments ()

If application.displaycommentindicator = Xlcommentandindicator Then

Application.displaycommentindicator = xlcommentindicatoronly

Else

Application.displaycommentindicator = Xlcommentandindicator

End If

End Sub

Application.displaycommentindicator has three states: xlcommentandindicator-always displays comment labels, xlcommentindicatoronly-Displays comment tags when the mouse points to the comment pointer of the cell, xlnoindicator-hides comment labels, and comment pointer for cells.

Iv. Change the default size of the comment label

SubCommentFitter1 ()

With Range ("A1"). Comment

. Shape.width = 150

. Shape.height = 300

End With

End Sub

Note: The Range.notetext method in the previous version can also return comment in the cell, and as described in the Help documentation for Excel, it is recommended that the Range.comment method be used uniformly in the new version.