Resizing Comment boxes back to default size!
 

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

Resizing Comment boxes back to default size!
 

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.

--

Vasant

"Jeff Etcell" wrote in message
...
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

Resizing Comment boxes back to default size!
 

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.

-----Original Message-----
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.

--

Vasant

"Jeff Etcell" wrote
in message
...
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


.

Resizing Comment boxes back to default size!
 

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



-----Original Message-----
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.

--

Vasant

"Jeff Etcell" wrote
in message
...
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


.

Resizing Comment boxes back to default size!
 

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
'=========================================

Jeff Etcell wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Resizing Comment boxes back to default size!
 

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
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

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

Resizing Comment boxes back to default size!
 

Debra,

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

Jeff
-----Original Message-----
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
'=========================================

Jeff Etcell wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.

Resizing Comment boxes back to default size!
 

You're welcome!

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

wrote:
Debra,

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

Jeff

-----Original Message-----
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
'=========================================

Jeff Etcell wrote:

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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Resizing Comment boxes back to default size!
 

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.

--

Vasant


"Debra Dalgleish" wrote in message
...
You're welcome!

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

wrote:
Debra,

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

Jeff

-----Original Message-----
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
'=========================================

Jeff Etcell wrote:

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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Resizing Comment boxes back to default size!
 

shhhh.

I stole it from Dana DeLouis.



Debra Dalgleish wrote:

You're welcome!

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

wrote:
Debra,

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

Jeff

-----Original Message-----
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
'=========================================

Jeff Etcell wrote:

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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

--

Dave Peterson