vba添加批注根据内容自动调整批注大小

tackey

vba添加批注后是这样：



想做出这样的效果

想做出这样的效果

现在只能手动设置

手动设置批注的自动调整大小

求高手指点一下

xiangbaoan

Sub test1()
  Dim ar, Dict As Object, i As Long
  ar = Sheet2.Range("A1").CurrentRegion
  Set Dict = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(ar)
    Dict(ar(i, 1)) = Dict(ar(i, 1)) & Chr(10) & ar(i, 2) & Space(2) & ar(i, 3)
  Next
  With Sheet1
    With .Range("A1").CurrentRegion.Columns(1)
      .ClearComments
      ar = .Value
    End With
    For i = 2 To UBound(ar)
      If Dict.Exists(ar(i, 1)) Then
        With .Cells(i, 1)
          .AddComment
          .Comment.Text Mid(Dict(ar(i, 1)), 2)
          .Comment.Shape.TextFrame.AutoSize = True
        End With
      End If
    Next
  End With
  Set Dict = Nothing
End Sub

tackey

xiangbaoan 发表于 2022-7-4 16:01
Sub test1()
  Dim ar, Dict As Object, i As Long
  ar = Sheet2.Range("A1").CurrentRegion

太牛了！