Working With Comments in Excel

March 3rd, 2008

Here's a few lines of code that demonstrates how to hide, show, or delete all comments in the activesheet. You can easily tweak the code to make it work on sheets other than the activesheet.

Sub HideAllComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
        c.Visible = False
    Next c
End Sub
Sub ShowAllComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
        c.Visible = True
    Next c
End Sub
Sub DeleteAllComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
        c.Delete
    Next c
End Sub

Or, maybe if you want comments written by yourself to stand out, change the comment shape from a rectangle to any available shape.

Sub HighlightMyComments()
    Dim c As Comment
    For Each c In ActiveSheet.Comments
        c.Visible = True
        If c.Author = Application.UserName Then
            c.Shape.AutoShapeType = msoShape16pointStar
        End If
    Next c
End Sub

With the above code, comments that you authored will show like this:

16 point star comment

Posted in Excel, How To, VBA | 2 Comments




2 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Vergel, what do you use to code the VBA for your blog? I tried the one from mrexcel.com (http://www.mrexcel.com/vbaddin.shtml) but wasn’t happy with it.

    Thx,
    JP

    Comment by JP — April 2, 2008 #

  2. Hi JP,

    I use WordPress for my blog and for the code, I use a plugin called IG Syntax Hiliter. See it here:

    http://blog.igeek.info/wp-plugins/igsyntax-hiliter/

    Comment by Vergel — April 3, 2008 #

Leave a comment



(C) by Virgilio Adriano. All rights reserved. Powered by WordPress.
Entries and comments feeds.
It took 0.288 seconds to load this page.