WHACKY COMMENTS?

  • Thread starter Thread starter Randy Numbers
  • Start date Start date
R

Randy Numbers

I'm having trouble with comments resizing themselves to weird shapes --
sometimes much larger than needed; other times collapsed completely. Is this
a known "feeture"? Is there an easy way to automatically resize (via marco
perhaps)? Many thanks... ./RN
 
Previously posted by William, this should sort you out:-

Sub RewriteComments()
Application.ScreenUpdating = False
Dim c As Range, s As String, r As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
For Each c In r
s = c.NoteText
c.ClearComments
c.NoteText s
c.Comment.Visible = False
c.Comment.Shape.TextFrame.AutoSize = True
Next c
Application.ScreenUpdating = True
End Sub
 
Ken,

Thanks for your reply -- however, this seems to convert the comment to a
single line with no word wrap.

I did get another reply that helped. Thanks again! ../RN
 
Debra,

Thanks for the info... now a follow-on question: I would like to resize the
comment text to size 10 and maybe bold it.

I tried adding .selection.font.size=10 but that doesn't seem to do it.
Thanks.../RN
 
You can modify it to include font settings:

'===============================
Sub Comments_AutoSize_Format()
'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
With .TextFrame.Characters.Font
.Name = "Arial"
.Size = 10
.Bold = True
.ColorIndex = 5 '5=Blue
End With
If .Width > 300 Then
lArea = .Width * .Height
.Width = 200
' An adjustment factor of 1.1 seems to work ok.
.Height = (lArea / 200) * 1.1
End If
End With
Next ' comment
End Sub
'===================================
 
Back
Top