Error in code from ChatGTP

[AlexanderBB]

Sub FormatComment(TheCell)
    Dim cell As Range
    Dim commentShape As Shape
    Dim desiredWidth As Single

    ' Set your desired comment width (in points)
    desiredWidth = 600

    ' Specify the cell containing the comment
    Set cell = ActiveSheet.Range(TheCell)

    ' Check if the cell has a comment
    If Not cell.Comment Is Nothing Then
        ' Access the comment's shape
        Set commentShape = cell.Comment.Shape

        ' Set the comment's width
        commentShape.Width = desiredWidth

        ' Ensure AutoSize is disabled to allow word wrap
        commentShape.TextFrame.AutoSize = False

        ' Optional: Adjust the height to fit the text
        commentShape.Height = commentShape.TextFrame.TextRange.BoundHeight + 10
    Else
        MsgBox "The specified cell does not contain a comment."
    End If
End Sub

Wordrap and width are good but the line commentShape.Height = commentShape.TextFrame.TextRange.BoundHeight + 10
Causes error
Run-time error '438':
Object doesn't support this property or method

Using Excel 2016. Height is critical, can i resolve this anyhow?

 

[bebo021999]

Is the "BoundHeight" property is available in Excel?
I am not at PC then I can not check, but you can try.

 

[AlexanderBB]

I assumed so as desciption starts with In Excel VBA, you can....
But .TextRange.BoundHeight doesn't appear in the dropdown list.
Maybe newer versions only ? Can I get height some other way?
In order to have Wordwrap autosize must be false. So the height returned is the actual height, not the height needed to see all the text.

 

[MARK858]

just...

commentShape.Height = commentShape.Height + 10

 

[RoryA]

and this is why we don't allow answers created with AI... That property exists in PowerPoint, not Excel.

 

[AlexanderBB]

> commentShape.Height = commentShape.Height + 10
Although that fixes the error, it doesn't help. You need Autosize false to wordwarp and then height returns a meaningless value.
You can add 10 to it but it still truncates the text.
I found ChatGPT quite interesting and useful until this. It also said
determining the number of rows it expands to is not straightforward. However, here are some approaches:
Measuring the text length.
Checking the font size and control width.
Estimating how many lines the text will wrap into.
A bit hit and miss and dependent on font size, proportional spacing etc. Has anyone succeeded with that?
I'll PowerPoint. It uses VBA. Maybe see how accurate TextFrame.TextRange.BoundHeight is and use it's value in Excel.