Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comment - How to write a macro to move & size cell (XP)

    TGIF,
    Could you please help me write a macro to move and size Comments.
    Thanks,

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

    Re: Comment - How to write a macro to move & size cell (XP)

    The position and size of a comment can be changed through its Shape property. For example:

    With Range("D3").Comment.Shape
    .Top = 100
    .Left = 140
    .Width = 200
    .Height = 150
    End With

    If you want more specific help, please explain what you want to accomplish.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comment - How to write a macro to move & size cell (XP)

    It may be best to send a sample workbook to show the context of your question!! Could you be more specific as to where you want to move them to and why?
    Tx
    Regards,
    Rudi

  4. #4
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment - How to write a macro to move & size cell (XP)

    Good morning Hans - Sorry I did not fully explain what I wanted (still had not had my first cup of Java)
    I have a huge spreadsheet that includes comments in the cells for over half of the spreadsheet. I would like to run a macro that would change the format of the comments from "Do not move or size" to "Move and size". Is there a way to do this?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Comment - How to write a macro to move & size cell (XP)

    Hi,
    You would use:
    Comment.Shape.Placement = xlMoveandSize

    To do all of them at once:
    Dim objComment as Comment
    for each objComment in activesheet.comments
    objcomment.shape.placement = xlmoveandsize
    next objComment

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Comment - How to write a macro to move & size cell (XP)

    Oh, OK. Here is a macro to do that. Make sure the worksheet with the comments is active when you run the macro.

    Sub MoveSizeComments()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
    cmt.Shape.Placement = xlMoveAndSize
    Next cmt
    Set cmt = Nothing
    End Sub

  7. #7
    Lounger
    Join Date
    Apr 2005
    Location
    Smyrna, Georgia, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comment - How to write a macro to move & size cell (XP)

    Worked beautifully.............. have a wonderful day!
    Lania

Posting Permissions

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