调整excel注释大小以适合具有特定宽度的文本

I'd like to have the comment box fit the comments just right (no extra space at the bottom).

我想让评论框恰好符合评论（底部没有额外的空格）。

I know there is the .AutoSize but I want the maximum width to be 300.

我知道有.AutoSize但我希望最大宽度为300。

Here is the code I have,

这是我的代码，

For Each mycell In myRng.Cells
    If Not (mycell.Comment Is Nothing) Then
      With mycell.Comment.Shape
        .TextFrame.AutoSize = True
        If .width > 300 Then
          lArea = .width * .height
          .width = 300
          .height = (lArea / 300)
        End If
      End With
    End If
Next mycell

mycell and myRng are Range datatypes, lArea is Long.

mycell和myRng是Range数据类型，lArea是Long。

Now, this works relatively well but leaves extra space at the bottom of a number of comments because the area the AutoSized text takes up is different from the area of the AutoSized comment box.

现在，这样做效果相对较好，但在多个注释的底部留下了额外的空间，因为AutoSized文本占用的区域与AutoSized注释框的区域不同。

Is there a way to check for blank space inside a comment and then trim it? Or is what I have the best it is going to be?

有没有办法检查评论中的空白区域然后修剪它？或者是我最好的将是什么？

1 个解决方案

#1

try this ... test comment has been placed in cell E4

试试这个...测试评论已被放置在单元格E4中

discovered by putting Range("e4").Comment.Shape.TextFrame in the Watch window

通过在Watch窗口中放置Range（“e4”）。Comment.Shape.TextFrame来发现

Sub testComment()

    With Range("e4").Comment.Shape

        .TextFrame.AutoSize = True

        lArea = .Width * .Height

        .Width = 300
        .Height = (lArea / .Width)       ' used .width so that it is less work to change final width

        .TextFrame.AutoMargins = False
        .TextFrame.MarginBottom = 0      ' margins need to be tweaked
        .TextFrame.MarginTop = 0
        .TextFrame.MarginLeft = 0
        .TextFrame.MarginRight = 0
    End With
End Sub

#1