On 2002-04-25 02:04, LisaA wrote:
Is it possible to change the default size (and position in relation to a cell) of a comment box within Excel? Alternatively, can I use a VBA macro to this ? I have written a macro which inserts comments into a worksheet but for this particular application I find the comment boxes too small. Unfortunately when I have tried to change the size and location within VBA, my macro hs failed.
Any suggestions gratefully received.
Many thanks.
Lisa.
Hi Lisa
here is some code to change the comments
size for all comments...just follow the
prompts.
<pre/>
'if you want to resize ALL comment boxs
'in an activesheet OR All sheets then this routine
'may help you. Resizing 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
</pre>
