Determining whether a cell has a comment

The following statement will display the comment in cell A1 of the active sheet:

MsgBox Range("A1").Comment.Text

If cell A1 does not have a comment, executing this statement will generate a cryptic error message: Object variable or With block variable not set.

To determine whether a particular cell has a comment, you can write code to see whether the Comment object is Nothing. (Yes, Nothing is a valid keyword.) The following statement displays True if cell A1 does not have a comment:

MsgBox Range("A1").Comment Is Nothing

Note that I use the Is keyword and not an equal sign.

You can take this one step further, and write a statement that displays the cell comment only if the cell actually has a comment (and does not generate an error if the cell lacks a comment). The statement below accomplishes this task:

If Not Range("A1").Comment Is Nothing Then _ MsgBox Range("A1").Comment.Text

0 0

Post a comment