Excel Cell Comments/Notes in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-400593%22%20slang%3D%22en-US%22%3EExcel%20Cell%20Comments%2FNotes%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-400593%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20retrieve%20Note%2FComment%20Cell%20text%20using%20VBA.%20I%20have%20no%20problem%20getting%20the%20text%20for%20a%20cell%20note%20but%20I%20have%20not%20found%20a%20way%20to%20get%20the%20text%20for%20a%20cell%20comment.%3C%2FP%3E%3CP%3EExample%20code.%3C%2FP%3E%3CP%3Estr%20%3D%20Worksheets(1).Range(%22A1%22).Comment.Text%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eworks%20fine%20if%20the%20cell%20contains%20a%20note%20but%20not%20if%20it%20contains%20a%20comment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20retrieve%20the%20comment%20text%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EJan%20Auzins%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-400593%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882260%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20Cell%20Comments%2FNotes%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882260%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F144213%22%20target%3D%22_blank%22%3E%40Jan%20Auzins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EAdd%20%22Threaded%22%20to%20%22Comment%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOld%3A%3C%2FP%3E%3CP%3E%3CSPAN%3Estr%20%3D%20Worksheets(1).%20Range(%22A1%22).%20Comment.Text%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ENew%3A%3C%2FP%3E%3CP%3E%3CSPAN%3Estr%20%3D%20Worksheets(1).%20Range(%22A1%22).%20CommentThreaded.Text%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps%3C%2FP%3E%3CP%3EDataVision%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-885256%22%20slang%3D%22en-US%22%3EBetreff%3A%20Excel%20Cell%20Comments%2FNotes%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F417304%22%20target%3D%22_blank%22%3E%40DataVision%3C%2FA%3E%26nbsp%3BThat's%20great%2C%20thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-927472%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Cell%20Comments%2FNotes%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-927472%22%20slang%3D%22en-US%22%3E%3CP%3EAlong%20similar%20lines%2C%20does%20any%20know%20how%20to%20extract%20notes%20(not%20comments)%20from%20cells%3F%20I've%20searched%20the%20net%20endlessly%20and%20found%20a%20formula%20for%20%3Dgetcomment(b2)%20for%20example%2C%20but%20this%20does%20not%20work.%20I%20specifically%20want%20to%20extract%20notes%20from%20cells%20in%20timesheets%20where%20a%20number%20of%20hours%20are%20listed%20and%20have%20the%20notes%20for%20that%20time%20output%20in%20other%20cells%20that%20I%20designate%20for%20the%20notes.%20I've%20watched%20YouTube%20videos%20but%20for%20some%20reason%20it%20is%20not%20working.%20I%20use%20O365%20so%20I%20am%20on%20the%20most%20current%20version%20of%20Excel.%20Can%20anyone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am trying to retrieve Note/Comment Cell text using VBA. I have no problem getting the text for a cell note but I have not found a way to get the text for a cell comment.

Example code.

str = Worksheets(1).Range("A1").Comment.Text

 

works fine if the cell contains a note but not if it contains a comment.

 

Is there a way to retrieve the comment text?

 

Regards

Jan Auzins

3 Replies
Highlighted

@Jan Auzins 

Hi!

Add "Threaded" to "Comment"

 

old:

str = Worksheets(1).Range("A1").Comment.Text

new:

str = Worksheets(1).Range("A1").CommentThreaded.Text

 

Hope this helps

DataVision

 

Highlighted

@DataVision That's great, thank you.

Highlighted

Along similar lines, does any know how to extract notes (not comments) from cells? I've searched the net endlessly and found a formula for =getcomment(b2) for example, but this does not work. I specifically want to extract notes from cells in timesheets where a number of hours are listed and have the notes for that time output in other cells that I designate for the notes. I've watched YouTube videos but for some reason it is not working. I use O365 so I am on the most current version of Excel. Can anyone help?