cell comment

firuz · Mar 19th, 2002, 11:52 AM

can i use any formula retrieving comments of specified cell?

Jack in the UK · Mar 19th, 2002, 05:26 PM

Hi --

Bit late this

No formula i know of will do this, VBA can list comments to a txt file or doc or cell i guess

Does this help, once did some work on that..

Tom Urtis · Mar 20th, 2002, 12:07 AM

This recent thread might help, though the solutions are VBA based, not formula based as you are asking for.

http://www.mrexcel.com/board/viewtop...c=2569&forum=2

Dave Hawley · Mar 20th, 2002, 03:18 AM

Hi firuz

Here is a Custom function that should do it. To use it Open the VBE (ALt+F11) go to insert>module and paste in this:

Function CommentText(rCellWithComment As Range)
CommentText = _
WorksheetFunction.Clean(rCellWithComment.Comment.Text)
End Function

Then just use it like:

=CommentText(A1)

Where A1 has a comment

Jack in the UK · Mar 20th, 2002, 03:29 PM

Dave

Anyway to braek as lines in cell comment might, instead of one long line??

Very cool i add!

Dave Hawley · Mar 20th, 2002, 07:31 PM

Hi Jack

There is no doubt some code I could include that would do so but it would be at the sacrafice of re-calulation speed. I think UDF's are slow enough already and like array formulas can cause more problems than they solve. Unfortunately most users discover this after the fact.

Best way would be to simply Wrap text to suit the cell size.

Jack in the UK · Mar 21st, 2002, 03:25 PM

Hi Dave

Your right again of cause, i just loved this and feel its a top bit of every ones tool kit as it was. A muct have!

Yep wrap is the way to go....

Cheers again Dave .... speak soon.

Your friend in London

Mar 19th, 2002, 05:26 PM	#2
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Hi -- Bit late this No formula i know of will do this, VBA can list comments to a txt file or doc or cell i guess Does this help, once did some work on that.. __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Mar 20th, 2002, 12:07 AM	#3
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,388	This recent thread might help, though the solutions are VBA based, not formula based as you are asking for. http://www.mrexcel.com/board/viewtop...c=2569&forum=2 __________________ Tom Urtis Microsoft MVP - Excel

Mar 20th, 2002, 03:29 PM	#5
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Dave Anyway to braek as lines in cell comment might, instead of one long line?? Very cool i add! __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Mar 21st, 2002, 03:25 PM	#7
Jack in the UK Board Regular Join Date: Feb 2002 Posts: 3,065	Hi Dave Your right again of cause, i just loved this and feel its a top bit of every ones tool kit as it was. A muct have! Yep wrap is the way to go.... Cheers again Dave .... speak soon. Your friend in London __________________ Free Excel based Web Toolbar available here. Jack in the UK J & R Excel Solutions "making Excel work for you"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 11:52 AM	#1
firuz New Member Join Date: Mar 2002 Posts: 7	can i use any formula retrieving comments of specified cell?

Mar 20th, 2002, 03:18 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi firuz Here is a Custom function that should do it. To use it Open the VBE (ALt+F11) go to insert>module and paste in this: Function CommentText(rCellWithComment As Range) CommentText = _ WorksheetFunction.Clean(rCellWithComment.Comment.Text) End Function Then just use it like: =CommentText(A1) Where A1 has a comment

Mar 20th, 2002, 07:31 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Jack There is no doubt some code I could include that would do so but it would be at the sacrafice of re-calulation speed. I think UDF's are slow enough already and like array formulas can cause more problems than they solve. Unfortunately most users discover this after the fact. Best way would be to simply Wrap text to suit the cell size.