Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Changing default size of comment boxes

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    dam Ivan good!

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •