Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need feature to change font size default in COMMENT boxes. | Excel Discussion (Misc queries) | |||
How do I change default font size for comment boxes in Excel? | Excel Discussion (Misc queries) | |||
Resizing comment boxes | Excel Programming | |||
Can we write VBA code to set all column/row's size back to default size? | Excel Programming | |||
Can we write VBA code to set all column/row's size back to default size? | Excel Programming |