Results 1 to 3 of 3
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Working with comment cells (XL2000)

    I am trying to adjust all cell comments on a sheet to font size 8 using VBA.

    I've tried using the Comments collection, tried using Shape.
    Can't seem to get the right combination.

    Any ideas?

    zeddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working with comment cells (XL2000)

    It's buried deep in the object hierarchy:

    A Comment has a Shape.
    A Shape has a TextFrame.
    A TextFrame has Characters.
    Characters have a Font.
    A Font has a Size.

    Still with me? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    So try something like this:

    Sub ChangeCommentFont()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
    cmt.Shape.TextFrame.Characters.Font.Size = 8
    Next cmt
    End Sub

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Working with comment cells (XL2000)

    Many thanks Hans!

    My problem was in trying
    cmt.Shape.TextEffect.FontSize = 8

    Note that FontSize is 'one word" when used with TextEffect and is Font-dot-Size when used with TextFrame!
    Drives me crazy!

    Your solution worked a treat - many thanks again.

    zeddy

Posting Permissions

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