×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using VBA to format Excel comments

Using VBA to format Excel comments

Using VBA to format Excel comments

(OP)
Currently, I have a spreadsheet that puts comments on a group of cells.  However, I want to change the font (size and bold) and also use the Autosize feature for the comment (textbox).  I tried recording a macro and manually changing the comment properties.  However, when I use the recorded code in my subroutine, I either get an error, or modify the cell properties (not the comment associated to the cell).

While searching the internet, it appears that the only way to accomplish this is to change the "Tooltip" configuration for the Windows enviornment.  This seems absurd to me, and am hoping someone can point me in the right direction.

Thanks!

RE: Using VBA to format Excel comments

(OP)
No sooner had I posted this question, that I found the answer (of course).  Here is a sample of the my code for anyone that is interested.

 With Sheet4.Cells(Row, Col).AddComment
                    .Visible = False
                    .Text comment
                End With
                With ActiveCell.comment.Shape.TextFrame.Characters.font
                    .Size = Font_Size
                    .Bold = True
                End With
                With ActiveCell.comment.Shape.TextFrame
                    .AutoSize = True
                End With

RE: Using VBA to format Excel comments

Good work on your own question.

Funny enough I was just about to post a similar question - about the resizing. I'll try out your code.

Thanks
Mogens

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources