Macro to resize a comment box
Posted by Ben on May 05, 2001 3:31 AM
How do I get a macro to resize a comment box?
Posted by anon on May 05, 2001 4:38 AM
You can get the code by using the macro recorder.
Posted by Dave Hawley on May 05, 2001 4:40 AM
'Written by OzGrid Business Applications
Dim CBox As Comment
Set CBox = Range("F10").Comment
.Text Text:="OzGrid Business Applications:"
.Shape.ScaleWidth 1.49, msoFalse, msoScaleFromBottomRight
.Shape.ScaleHeight 1.24, msoFalse, msoScaleFromBottomRight
Set CBox = Nothing
OzGrid Business Applications
Posted by Ivan Moala on May 05, 2001 7:20 AM
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.
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
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
'lock aspect to get even increase in size
.Shape.LockAspectRatio = True
'increase by 25% change as required
.Shape.Height = .Shape.Height * 1.25
Set allComments = Nothing
Set Sh = Nothing
Posted by Aladin Akyurek on May 05, 2001 7:55 AM
Off topic...more or less
Ivan -- I introduced you to a site for Dutch accountants & accountancy students. See:
And also a long overdue: Welcome back.
Posted by Ivan Moala on May 05, 2001 8:01 AM
Re: Off topic...more or less
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.
Posted by Ben on May 05, 2001 11:07 AM
Thanks all of you