Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-sizing C. Boxes using Debra Dalgleish
Hi. Excel 2003
3 questions 1. I have about 300 comment boxes to format. I just need to make them all the same size. I'm not worried about anything else. Debra's code should work, but it makes them about .3" inches by .3" inches. Can anyone help me adapt her code to make my boxes 1.8" tall by 1.5" wide. Here is her code: 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 2. How do I enter the code? I assume I right click on the sheet tab,select view code, copy and paste. Then do I need to click run or do I just save the code to my spreadsheet and it will take care of itself. 3. I would imagine that I need to make sure that all cells are unlocked? Thanks in advance -- vze2mss6 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-sizing C. Boxes using Debra Dalgleish
Here's a tinker* with Dave's sub for you to try out ..
*Sub FixedResizeCommentsInSelection() Setting it up .. In a copy of your book (always test-run subs on a copy first) Steps -------- Press Alt+F11 to go to VBE Click Insert Module Copy paste everything within the dotted lines below into the code window (whitespace) on the right '---- Sub FixedResizeCommentsInSelection() 'Resizes comments in selected range to 1.8" height, 1.5" width 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.Height = 129.75 .Shape.Width = 108# End With End If Next mycell End Sub '---- Press Alt+Q to get back to Excel In Excel, 1. Select the range on the sheet which contains the comments 2. Press Alt+F8 (brings up the Macro dialog) Select "FixedResizeCommentsInSelection" click "Run" (or just double-click directly on "FixedResizeCommentsInSelection") The comments should be resized as desired -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "joesf16" wrote: Hi. Excel 2003 3 questions 1. I have about 300 comment boxes to format. I just need to make them all the same size. I'm not worried about anything else. Debra's code should work, but it makes them about .3" inches by .3" inches. Can anyone help me adapt her code to make my boxes 1.8" tall by 1.5" wide. Here is her code: 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 2. How do I enter the code? I assume I right click on the sheet tab,select view code, copy and paste. Then do I need to click run or do I just save the code to my spreadsheet and it will take care of itself. 3. I would imagine that I need to make sure that all cells are unlocked? Thanks in advance -- vze2mss6 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Re-sizing C. Boxes using Debra Dalgleish
Here's a tinker* with Dave's sub for you to try out ..
*Sub FixedResizeCommentsInSelection() Setting it up .. In a copy of your book (always test-run subs on a copy first) Steps -------- Press Alt+F11 to go to VBE Click Insert Module Copy paste everything within the dotted lines below into the code window (whitespace) on the right '---- Sub FixedResizeCommentsInSelection() 'Resizes comments in selected range to 1.8" height, 1.5" width 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.Height = 129.75 .Shape.Width = 108# End With End If Next mycell End Sub '---- Press Alt+Q to get back to Excel In Excel, 1. Select the range on the sheet which contains the comments 2. Press Alt+F8 (brings up the Macro dialog) Select "FixedResizeCommentsInSelection" click "Run" (or just double-click directly on "FixedResizeCommentsInSelection") The comments should be resized as desired -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "joesf16" wrote: Hi. Excel 2003 3 questions 1. I have about 300 comment boxes to format. I just need to make them all the same size. I'm not worried about anything else. Debra's code should work, but it makes them about .3" inches by .3" inches. Can anyone help me adapt her code to make my boxes 1.8" tall by 1.5" wide. Here is her code: 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 2. How do I enter the code? I assume I right click on the sheet tab,select view code, copy and paste. Then do I need to click run or do I just save the code to my spreadsheet and it will take care of itself. 3. I would imagine that I need to make sure that all cells are unlocked? Thanks in advance -- vze2mss6 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Debra Dalgleish Question | Excel Worksheet Functions | |||
Bless you, Debra Dalgleish & Pejo Sjoblom! | Excel Worksheet Functions | |||
Debra Dalgleish | Excel Discussion (Misc queries) | |||
Debra Dalglish | Excel Discussion (Misc queries) | |||
Multiple Consolidation Ranges - Debra Dalgleish | Excel Discussion (Misc queries) |