Excel VBA Get Comment from Cell

[JStellato]

Hello, I'm having trouble extracting a comment from a cell, here's my code:

Dim varComment As String
varComment = Cells(A1).Comment.Text
MsgBox varComment

 

[mikerickson]

That looks like good code. What is the problem?
Is there actually a comment in A1 of the active worksheet?

 

[VoG]

Try like this

Sub Test()
Dim varComment As String
Dim c As Comment
With Range("A1")
    On Error Resume Next
    Set c = .Comment
    On Error GoTo 0
    If c Is Nothing Then
        MsgBox "No comment", vbExclamation
    Else
        varComment = c.Text
        MsgBox varComment, vbInformation
    End If
End With
End Sub

 

[JStellato]

Thanks, this worked!

 

[men5j2s]

So if i want to adapt this for another purpose, how would it work... where is the destination cell in this case?

I want to display the comments of a a cell based on a criteria, but I'm not sure how to revert to the cell comments.

I can't even display A1 comments in another cell on a test sheet....

 

[JStellato]

So if i want to adapt this for another purpose, how would it work... where is the destination cell in this case?

I want to display the comments of a a cell based on a criteria, but I'm not sure how to revert to the cell comments.

I can't even display A1 comments in another cell on a test sheet....

I'm not sure what you mean "display A1 comments", do you mean show the comments programmatically?

Or do you mean copy the comments to a variable?

Or show comments in a message box?

 

[men5j2s]

Sorry my last comment "I can't even display A1 comments in another cell on a test sheet...." meant that i couldn't get your formula to work.

I have sorted it now after a little tweeking. I wanted to display the cell comments from a given cell based on a look up of another cell in the same row.

i.e. search a name and return the comments for the cell attached to the searched name in a database.

Could you possibly help me with another problem? I'm not getting much help on it and can't seem to solve it...

http://www.mrexcel.com/forum/excel-...ations-automate-sheet-sizing-not-working.html

thank you

 

[Simont485]

Hi there...I have been using various get comments vba codes but i have this problem with all of them.

When they are first loaded they work perfectely and I can conditiona format the cells to suit my needs...all good so far.

But when I log back in to a PC the 'getcomment' function no longer works and I just get a error message. If i re-link all the cells again it will work but only for that session...I have to re-link each time.

Should this happen and how to stop it??

 

[tnavarre]

Hi, I'm also using this same code, working just fine. I'm having one issue, and I can't seem to get around it. I'm trying to get comments where there are two bodies of text at each line, no space between. In other words, one line is written, enter is clicked, and the next body is written. When I use my function, the comment text generates, but both bodies are together, so it looks messy. Is there any way this can be fixed? This is the code I'm currently using

Function getComment(rng As Range) As String
    Application.Volatile
    Dim str As String
    If rng.Comment Is Nothing Then
    getComment = ""
    Else
    str = rng.Comment.Text
    str = Right(str, Len(str) - InStr(1, str, ":") - 1)
End If
    getComment = str
End Function

I appreciate the help!

 

[RoryA]

Have you formatted the formula cell with the Wrap Text option?