Skip to content

Instantly share code, notes, and snippets.

@z720

z720/ResetComments.bas

Created February 1, 2022 13:20
Show Gist options
  • Save z720/1a5ece32d6174ac7b49fae5c9e8f0c7c to your computer and use it in GitHub Desktop.
Save z720/1a5ece32d6174ac7b49fae5c9e8f0c7c to your computer and use it in GitHub Desktop.
Download ZIP
Excel Macro : Reset comments (position and size)
Raw
ResetComments.bas
Attribute VB_Name = "ResetComments"
Sub ResetComments()
' ADD TO %appdata%\Microsoft\Excel\XLSTART\PERSONAL.XLSB
Dim pComment As Comment
Dim maxWidth As Integer
maxWidth = 250
For Each pComment In Application.ActiveSheet.Comments
pComment.Shape.Top = pComment.Parent.Top + 15
pComment.Shape.Left = pComment.Parent.Offset(0, 1).Left + 25
pComment.Shape.TextFrame.AutoSize = True
If pComment.Shape.Width > maxWidth Then
Dim area As Long
area = pComment.Shape.Height * pComment.Shape.Width
pComment.Shape.Height = area / maxWidth
pComment.Shape.Width = maxWidth
End If
Next
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment