Changing default size of comment boxes

[LisaA]

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.

[Cheers. Thanks for the response - I'll give it a try!]

[ This Message was edited by: LisaA on 2002-04-25 02:41 ]

[Ivan F Moala]

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.

'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

[Jack in the UK]

dam Ivan good!