Re-sizing C. Boxes using Debra Dalgleish

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Back
Top