Macro to resize a comment box

You can get the code by using the macro recorder.

Hi Ben

Run this:

Sub ResizeComment()
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim CBox As Comment
Set CBox = Range("F10").Comment

With CBox
.Text Text:="OzGrid Business Applications:"
.Shape.ScaleWidth 1.49, msoFalse, msoScaleFromBottomRight
.Shape.ScaleHeight 1.24, msoFalse, msoScaleFromBottomRight
End With

Set CBox = Nothing
End Sub

OzGrid Business Applications

Ben if you want to resize ALL comment boxs
in an activesheet OR All sheets then this routine
may help you. Rsizing is @ 25% change as required.
Or scale down.

Sub ChangeSize_Comments_SSh()
Dim cCell As Range
Dim sComment As Comment
Dim allComments As Range
Dim Sh As Worksheet
Dim Ans As Integer
Dim All As Boolean

Ans = MsgBox("Activesheet (Yes) or ALL sheets (No)", vbYesNoCancel)
If Ans = 2 Then Exit Sub

All = IIf(Ans = 7, True, False)

If Not All Then Set Sh = ActiveSheet: GoTo skipSh

For Each Sh In ActiveWorkbook.Sheets

skipSh:
On Error Resume Next
Set allComments = Sh.Range("A1").SpecialCells(xlCellTypeComments)
If allComments Is Nothing And Not All Then MsgBox "No comments in " & ActiveSheet.Name: GoTo Ex
'Leave On Error Resume next ON to take care of Loop not set error
'when doing active sheet only.

For Each cCell In allComments
With cCell.Comment
'lock aspect to get even increase in size
.Shape.LockAspectRatio = True
'increase by 25% change as required
.Shape.Height = .Shape.Height * 1.25
End With
Next cCell
Next Sh

Ex:
Set allComments = Nothing
Set Sh = Nothing

End Sub

Ivan

Off topic...more or less

Ivan -- I introduced you to a site for Dutch accountants & accountancy students. See:

http://www.accountingweb.nl/cgi-bin/item.cgi?id=43565&d=101&h=0&f=0&dateformat=%o%20%B%20%Y

And also a long overdue: Welcome back.

Aladin

Re: Off topic...more or less

Aladin
Great....many thanks for that. I see that you
have been busy on this site. Keep up the great
work. I'm sure you have helped a lot of people.

regards

Ivan

Thanks all of you

Thanks