Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleUse VBA to place a comment on a cell in Excel
DescriptionThis example shows how to use VBA to place a comment on a cell in Excel.
KeywordsExcel, comment, VBA, set comment, clear comment
CategoriesOffice
 
giving a cell a comment isn't quite as easy as you might expect. If a cell does not yet have a comment, then trying to look at it causes the code to crash. Instead you must see if the comment is Nothing to learn if it is present.

The SetComment subroutine sets or clears a cell's comment. The range passed into the subroutine should be a single cell. If it is not, then the routine sets the comment for the range's first cell.

First the subroutine checks the new comment text's length to see if it is setting or clearing a comment. Pass in a blank string to clear a comment.

If the subroutine should clear any existing comment, the code checks whether the comment is Nothing. If the comment is not Nothing, the code calls the comment's Delete method to delete it.

If the new comment text is non-blank, the code checks whether the comment is Nothing. If the comment is Nothing, the code calls the cell's AddCommenth method to make the comment. If the comment is not Nothing, then the code calls the comment's Text method to set its new text.

 
' Put a comment in a Range.
Private Sub SetComment(ByVal the_cell As Range, ByVal txt _
    As String)
    ' See if the text is blank.
    If Len(txt) = 0 Then
        ' Clear any existing comment.
        ' See if the cell has a comment.
        If Not (the_cell.Comment Is Nothing) Then
            the_cell.Comment.Delete
        End If
    Else
        ' Set the comment.
        ' See if the cell has a comment.
        If the_cell.Comment Is Nothing Then
            the_cell.AddComment txt
        Else
            the_cell.Comment.Text txt
        End If
    End If
End Sub
 
 
Copyright © 1997-2006 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated