Excel Comments – Macros

Excel Comments – Macros

Source: Excel Comments – Macros

‘Kudos: https://www.contextures.com/xlcomments03.html
Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
cmt.Shape.Top = cmt.Parent.Top + 5
cmt.Shape.Left = _
cmt.Parent.Offset(0, 1).Left + 5
Next
End Sub
Sub Comments_AutoSize()
‘posted by Dana DeLouis 2000-09-16

Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
‘ An adjustment factor of 1.1
‘ seems to work ok.
.Shape.Height = (lArea / 200) * 1.1
End If
End With
Next ‘ comment
End Sub

Advertisements