Resizing Comment boxes back to default size!

[Jeff Etcell]

I have a problem whereas my comment boxes resized to
nothing and moved positions. I have the position issued
rectified (with help from Paul) but need to know how to
resize my comment boxes back to a default size and I have
over 500 on one worksheet.
Can this be done?

Thanks

 

[Vasant Nanavati]

Are you sure you don't have a global macro or template resetting your
comment box size? AFAIK there's no way to change the default size.

 

[Jeff Etcell]

I had no macro at all when my comment boxes collapsed.
Besides they didn't resize to one size globally. Some
resized to 3 times the width while others resized to
nothing. Still confused here.

 

[Jeff Etcell]

Sorry I don't want to change the default size, I want to
change them BACK to the default size.

 

[Debra Dalgleish]

The following code will resize all the cells in the selected range:

'=========================
Sub ResizeCommentsInSelection()
'Posted by Dave Peterson
'2002-02-25
Dim mycell As Range
Dim myRng As Range
Dim lArea As Long

Set myRng = Selection

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

End Sub
'=========================================

 

[pfsardella]

Jeff,

I don't know how the default size is determined. I would creating a
new workbook and insert a comment. Then, with that cell selected, Alt
F11 to go to VBE. Then, on the Menubar, View > Immediate Window. Enter
the following statements to get the width and height.

?activecell.Comment.Shape.width
?activecell.Comment.Shape.height

Mine are 108 and 55.5, respectively.

Sub ChangeCommentSize()
Dim cmt As Comment
For Each cmt In ActiveWorkbook.ActiveSheet.Comments
With cmt
.Shape.Width = 108
.Shape.Height = 55.5
End With
Next cmt
End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul

 

[Guest]

Debra,

My appreciation for this remedy is immense and I thank you
so much for your time.

Jeff

 

[Debra Dalgleish]

You're welcome!

And my thanks to Dave, from whom I stole the code.

 

[Vasant Nanavati]

Hey! I could have taken the easy way out and stolen Dave's code too!

But I was curious as to why the *default* size changed and how it could be
changed back, since I've never heard of this behavior before.

 

[Dave Peterson]

shhhh.

I stole it from Dana DeLouis.

You're welcome!

And my thanks to Dave, from whom I stole the code.