Trying to change the attributes of a Cell's Comment object. The macro generated by Excel does not work

Question

I want to change the attributes of the text inside in Cell's Comment object. The automatically generated macro that Excel produced does not work. When running that macro I get the error "Object doesn't support this property or method". I fixed this by changing ".ShapeRange" to ".Shape". Next the error "Object doesn't support this action" was produce by "TextFrame2". I am providing the following 2 procedures of code. The first is the original the second is my attempt to solve the problem. However my code is generating the error "Method 'TextFrame' of object 'Shape' failed".

Sub OriginalMacro()

    Range("H25").Select
    Selection.ShapeRange.ScaleWidth 3.3647051779, msoFalse,   msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.3800319525, msoFalse, msoScaleFromTopLeft
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(2, 322).Font
        .NameComplexScript = "Times New Roman"
        .NameFarEast = "Times New Roman"
        .Name = "Times New Roman"
    End With
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(2, 322).Font.Size = 12
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters(2, 322).Font.Bold = _
        msoTrue
    Range("H25").Comment.Text Text:= _
        "Simply place positive numbers in any of the cells from G26 thru G43 directly above the cell that displays the total amount just to the right of the word ""Total;"". They will be added together and dispa"
    Range("H25").Comment.Text Text:= _
        "yed in the cell next to the word ""Total:"". " & Chr(10) & "" & Chr(10) & "If you want to subtract a number simply place a negative sign in front of it." & Chr(10) & "" _
        , Start:=200
    Selection.ShapeRange.ScaleHeight 1.1283545656, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0731088187, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleWidth 1.0473934127, msoFalse, msoScaleFromTopLeft
    Range("E32").Select
End Sub

Sub FormatCalcComment()

    Range("H25").Select
    Range("H25").Comment.Visible = True
    Range("H25").Comment.Shape.ScaleWidth 3.605455577, msoFalse, msoScaleFromTopLeft
    Range("H25").Comment.Shape.ScaleHeight 1.3080804104, msoFalse, msoScaleFromTopLeft
    'Range("H25").Comment.Select

    Range("H25").Comment.Shape.Select

    '*********The following line generates the error.**********
    Range("H25").Comment.Shape.TextFrame.Characters(1, 323).Font.Name = "Times New Roman"

        With Range("H25").Comment.Shape.TextFrame.Characters(1, 323).Font
            .NameComplexScript = "Times New Roman"
            .NameFarEast = "Times New Roman"
            .Name = "Times New Roman"
        End With

    Range("H25").Comment.Shape.TextFrame2.TextRange.Characters(1, 323).Font.Size = 12
    Range("H25").Comment.Shape.TextFrame2.TextRange.Characters(1, 323).Font.Bold = _
        msoTrue
    Range("H25").Comment.Text Text:= _
        "Simply place positive numbers in any of the cells from G26 thru G43 directly above the cell that displays the total amount just to the right of the word ""Total;"". They will be added together and displ"
    Range("H25").Comment.Text Text:= _
        "ayed in the cell next to the word ""Total:"". " & Chr(10) & "" & Chr(10) & "If you want to subtract a number simply place a negative sign in front of it." & Chr(10) & "" _
        , Start:=200
    Range("H25").Comment.Shape.ScaleWidth 1.055297049, msoFalse, msoScaleFromTopLeft
    Range("H25").Comment.Shape.ScaleHeight 1.2445311296, msoFalse, msoScaleFromTopLeft
    Range("H26").Select
End Sub

Should be Bold faced and changed to New Times Roman. And the frame resized to fit.

Are you updating the entire text content of the comment, or just a part of it? The `Characters` collection is only really useful up to about 255 characters, but you don't need it if you're updating the full content. — Tim Williams, Jan 30 '19 at 23:40
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Jan 31 '19 at 08:34

score 0 · Answer 1 · answered Jan 31 '19 at 03:43

A Small Comment Study

I couldn't find anything except the AutoSize property which will resize to the longest row of text, so you will have to write the text e.g. 40 characters maximally per line.
To place the comment, use the properties of the cell range (Top, Height, Left and Width) to adjust the same properties of the comment particularly the Top and Left properties, because the other will be changed by AutoSize.

The Code

Sub CommentExample()

    Const cSheet As String = "Sheet1"
    Const cStrRange As String = "H25"
    Const Sep As String = vbLf
    Const cMargin As Single = 5

    Dim Cell As Range
    Dim CommEx As Comment
    Dim str1 As String

    Set Cell = ThisWorkbook.Worksheets(cSheet).Range(cStrRange)

    ' Look in the Immediate Window
    With Cell
        str1 = "Cell 'Area' Properties"
        str1 = str1 & Sep & Space(2) & "Top = " & .Top
        str1 = str1 & Sep & Space(2) & "Height = " & .Height
        str1 = str1 & Sep & Space(2) & "Left = " & .Left
        str1 = str1 & Sep & Space(2) & "Width = " & .Width
        Debug.Print str1
    End With

    Set CommEx = Cell.Comment

    If CommEx Is Nothing Then
        MsgBox "No Comment in Cell '" & cStrRange & "'."
        Cell.AddComment
        Set CommEx = Cell.Comment
    End If

    With CommEx
        .Visible = True
        With .Shape.TextFrame.Characters
            .Delete
            With .Font
                .Name = "Times New Roman"
                .Size = 12
                .Bold = True
            End With
        End With

        ' Text Width:   "======================================== "
        .Text _
                        "Simply place positive numbers in any of " _
                & Sep & "the cells from G26 thru G43 directly " _
                & Sep & "above the cell that displays the total " _
                & Sep & "amount just to the right of the word " _
                & Sep & """Total"". They will be added together " _
                & Sep & "and displayed in the cell next to the " _
                & Sep & "word ""Total""." _
                & Sep _
                & Sep & "If you want to subtract a number simply " _
                & Sep & "place a negative sign in front of it."
        ' Text Width:   "======================================== "

        With .Shape

           ' Look in the Immediate Window
            str1 = "Comment 'Area' Properties (Before AutoSize)"
            str1 = str1 & Sep & Space(2) & "Top = " & .Top
            str1 = str1 & Sep & Space(2) & "Height = " & .Height
            str1 = str1 & Sep & Space(2) & "Left = " & .Left
            str1 = str1 & Sep & Space(2) & "Width = " & .Width
            Debug.Print str1

            With .TextFrame
                ' To use margins you have to set AutoMargins to False.
                .AutoMargins = False
                .MarginBottom = cMargin
                .MarginLeft = cMargin
                .MarginRight = cMargin
                .MarginTop = cMargin
                ' Depending on vbLf, the Autosize property will 'resize to fit'.
                .AutoSize = True
            End With

            ' Look in the Immediate Window
            str1 = "Comment 'Area' Properties (After AutoSize)"
            str1 = str1 & Sep & Space(2) & "Top = " & .Top
            str1 = str1 & Sep & Space(2) & "Height = " & .Height
            str1 = str1 & Sep & Space(2) & "Left = " & .Left
            str1 = str1 & Sep & Space(2) & "Width = " & .Width
            Debug.Print str1

            With .TextFrame
                ' Look in the Immediate Window
                str1 = "Margins"
                str1 = str1 & Sep & Space(2) & "MarginBottom = " & .MarginBottom
                str1 = str1 & Sep & Space(2) & "MarginLeft = " & .MarginLeft
                str1 = str1 & Sep & Space(2) & "MarginRight = " & .MarginRight
                str1 = str1 & Sep & Space(2) & "MarginTop = " & .MarginTop
                Debug.Print str1
            End With

        End With

    End With

End Sub

score 0 · Answer 2 · answered Jan 31 '19 at 03:49

Perhaps the sub below will solve part of your problem. Call it with code like, SetComment Cells(1, 1), "Comment text", True which would append "Comment text" to text already existing in a comment in cell A1. Define Concat as False to replace existing text.

Sub SetComment(Cell As Range, _
               Optional ByVal Txt As String, _
               Optional ByVal Concat As Boolean)
    ' 12 Jul 2018
    ' deletes existing comment if Txt = "" and Concat = False

    Dim Cmt As String

    With Cell
        On Error Resume Next
        Cmt = .Comment.Text
        .Comment.Delete
        On Error GoTo 0
        If Concat Then
            If Len(Cmt) Then Cmt = Cmt & Chr(10)
        Else
            Cmt = ""
        End If

        Txt = Cmt & Txt
        If Len(Txt) Then .AddComment Cmt & Txt
    End With
End Sub

Trying to change the attributes of a Cell's Comment object. The macro generated by Excel does not work

2 Answers2

A Small Comment Study

The Code