asked on

Detecting if a Comment is in a Cell

I would like to determine if a Cell already has a comment, prior to performing some other action.

I have tried:

With Sheet3 (and With Worksheets("LogFile"))

If .Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment.Visible = Visible Then
If .Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment.Text = "" Then
If Not (.Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment) Is Nothing Then
and a few others

End With

Maybe there was some combination that I missed, but each of these keep erroring out. In using VBA, what is the best syntax to use to determine if a Comment already exits in the in the Cell:

.Cells([nSht3LastRow], [nBlocksCol]).

There are values in both of the variables. Row = 15 Column = 15.

jppinto

Please try like this.

jppinto

If Sheets("Sheet3").Cells([nSht3LastRow], [nBlocksCol]).Comment.Text = "" Then
   MsgBox "Cell Has No Comment"
Else
   MsgBox "Cell Has comment"
End If

Open in new window

gplana

Have you tried this one?:

If .Range(.Cells(1, 1)).Comment.Text <> "" Then

which looks if there is a comment on Cell A1.

jppinto

Here's a little code for your to try it...

Sub HasComment()
    On Error GoTo nocomment
    If Not ActiveCell.Comment.Text = "" Then
        MsgBox "Cell Has comment"
    End If
    Exit Sub
nocomment:
    MsgBox "Cell Has No Comment"
End Sub

Open in new window

Patrick Matthews

Cook09,

If the cell has no comment, then the approaches above will generate a runtime error.

If Not .Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment Is Nothing Then
    'code for if comment is present
Else
    'code for no comment
End If

Open in new window

Patrick

Cook09

ASKER

On the first on there was a run-time error:
"The item with the specified name wasn't found."

The Second one also errors:
Method 'Range' of Object' _Worksheet' Failed

On the the third, since it's being done in VBA, there really is no ActiveCell, although, I did attempt to make it Active at one point and it still errored. Not with yours, but with another one.

Cook09

ASKER

Patrick
I still get the Method of Range Error...do I need to put On Error Resume Next?

jppinto

Please check the attached sample file. Select a cell and click the button to test. Test on cells that have comments and on cells that don't have. I tested the code and it works fine for me.

jppinto
Check-Comments-Exist-VBA.xlsm

Patrick Matthews

Cook09,

Please post the code exactly as you tried it.

Patrick

Cook09

ASKER

This what I've been trying:

With Sheet3
If Not .Range(.Cells([nSht3LastRow], [nBlocksCol])).Comment Is Nothing Then
Debug.Print "No Comment"
Else
Debug.Print "Comment In Cell"
End If
End With

Open in new window

Cook09

ASKER

jppinto,

I tied it and it does work. I copied it to a Utility Mod, selected a cell and it worked. I guess the trick, is using VBA it make a cell Active and run the program as such. I did try the ActiveCell after doing a:

.Range(.Cells([nSht3LastRow], [nBlocksCol])).Activate

and could not get it to work. Maybe I'll try the above and substitute it into yours.

Patrick Matthews

Try:

With Sheet3
If Not .Cells([nSht3LastRow], [nBlocksCol]).Comment Is Nothing Then
Debug.Print "No Comment"
Else
Debug.Print "Comment In Cell"
End If
End With

Open in new window

Cook09

ASKER

Patrick,
Yes, I'll try that.

jppinto,

I tried
With Sheet3
.Range(.Cells([nSht3LastRow], [nBlocksCol])).Activate
End With

and it failed with the rest of the code.

Cook09

ASKER

Patrick,
The two times I tried It, the procedure actually works in the reverse. If there is a Comment, it Prints the first one. When I deleted the Comment, it went to the second one. I'll retry, but could it just be reversed in what it prints?
Ron

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cook09

ASKER

Well, when all is said and Done, yours was the only one I got to work. It's interesting that in searching the Net, all the examples used were for global comments on a Sheet or Workbook. None addressed a single cell, except to .AddComment or .Delete.

I will add this to my personal Knowledge Base. But, do you know why the syntax is so particular with this one property?

Ron

Patrick Matthews

Ron,

It is because there is a Comment object, and if you try to address that object's properties (such as Text) when the object does not itself exist, you will get a runtime error.

That is, when you try a stement like this:

If MyRange.Comment Is Nothing

Open in new window

That Comment property is returning a Comment object. The line above is OK, because all it does is check existence of the object. If you try to go another layer down, and access properties of the Comment object, you get an error if that object does not exist.

Patrick

Cook09

ASKER

It's sometimes good having a Savant assisting in what would appear to be a simple thing on the surface. Thanks for helping me through.

I appreciate the other two Experts and what they attempted to bring to this, but it is far less obvious than what would be assumed.

Cook09

ASKER

Patrick,
I will also assume that there is no way around the error if one does attempt to access one of the properties?

I think, that in some literature somewhere, when trying to access a property that also could be an Object one could use something such as:

Range.Objects.Object.Properties

Is that correct or was I thinking about something else?

Ron

Patrick Matthews

Ron,

You could use an error handler, such as jppinto demonstrated, or even simply On Error Resume Next to ignore the error.

As for Range.Objects.Object.Properties, I am not aware of anything like that, but that does not necessarily mean that it doesn't exist.

Glad to help,

Patrick

Cook09

ASKER

Patrick,

The On Error Resume Next doesn't work as it defaults to True, but there could be others that may work. Thanks again.
Ron