How to determine if a cell's comment is blank or empty?

Hello,

I have searched for this and come up with nothing

I have tried the simple code below to see if it will identify if a cell's comment is blank or not. Is the use of 'Not IsEmpty(...)' the correct approach?

Sub test()
    Debug.Print Not IsEmpty(Cells(1, 1).Comment)
    If Not IsEmpty(Cells(1, 1).Comment) Then
        MsgBox ("this cell does not have a comment")
    ElseIf IsEmpty(Cells(1, 1).Comment) Then
        MsgBox ("this cell has a comment")
    End If
End Sub

Cheers,

TV

Originally Posted by Izandol

You may test if the Comment is Nothing:

    If Cells(1, 1).Comment Is Nothing Then
        MsgBox "this cell does not have a comment"
    Else
        MsgBox "this cell has a comment"
    End If

Thanks Izandol, however I would need to check first if the comment is Not Nothing. Is there a way to handle that?

TV

Originally Posted by Izandol

The code I provided does exactly that. It is to tell you if there is a comment or not.

Yes it does. However this is just a test to see what results are returned. What I'm really after is to check if a comment has a value and then...

So how would you use 'Is Nothing' to look for a comment field that has something in it?

Cheers,

TV

Originally Posted by JieJenn

An alternative I use is IF Trim(Len(Cells(1,1).Comment)) = 0

Hi JieJenn, that may be a better approach. I will give it a try.

Thanks,

TV

I gave the Trim(Len(... a try and I get a Run-time error '438'

Sub test()
    If Trim(Len(Cells(1, 1).Comment)) <> 0 Then
        MsgBox ("this cell has a comment")
    ElseIf Trim(Len(Cells(1, 1).Comment)) = 0 Then
        MsgBox ("this cell does not have a comment")
    End If
End Sub

Any ideas why?

TV

My apologies, I have asked the original question the wrong way. My real question is how to determine if a cells comment has a value in it?

TV

Izandol,

The Len(Cells.... works like a charm.

Thank you very much.

TV