Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

copy comments from one cell into adjacent cell 1

Status
Not open for further replies.

salmonman

Technical User
Joined
Apr 3, 2003
Messages
3
Location
IN
I am trying to copy the comments text from one cell into an adjacent cell. Is there an excel formula for this?
Ex..
Cell B3 (Value = 4, red triangle comment says "This means 4 apples"
I want cell C3 to have text in it saying "This means 4 apples".
Any help is appreciated. Thanks.
 
I don't know of one, but if you can cope with VBA, you can write your own like this:
Code:
Option Explicit
Function GetComment(ReferenceCell) As String
  GetComment = Mid(ReferenceCell.Comment.Text, _
      Len(ReferenceCell.Comment.Author) + 3, 999)
End Function
Unfortunately, it won't update automatically when the comment in the referenced cell is changed. But if I understand your request, that shouldn't be a problem.
 
Hey there - thanks for the reply.
Still having troubles though - I'm quite new at Visual basic. What I tried was copying the code into my personal.xls - and then inserting the function in excel by clicking on the function button - user defined - personal.xls-this workbook - Get comment.
Any suggestions?
 
So when you clicked on the function button and selected User Defined, did you see "GetComment"? Then what happens? Where exactly did you copy the code?

BTW, the function as written is "quick and dirty" -- if there is no comment, it returns #VALUE. This is a more robust version:
Code:
Option Explicit
Function GetComment(ReferenceCell) As String
Dim cComment As Comment
  Set cComment = ReferenceCell.Comment
  If cComment Is Nothing Then
    GetComment = ""
  Else
    GetComment = Mid(cComment.Text, _
        Len(cComment.Author) + 3, 999)
  End If
  Set cComment = Nothing
End Function
 
Yes - the 'getcomment' is in the user defined (well, it actually says Personal.xls!Thisworkbook.GetComment
Once selected... this baby comes up in the input field..
=PERSONAL.XLS!ThisWorkbook.GetComment()
As well as a prompt box that says.
"Choose the Help button for help on this funciton and it's arguments.
This function takes no arguments".
I tried putting my cell I want to get the comment from (ex. C9) in the equals line... but it doesn't seem to work.
=PERSONAL.XLS!ThisWorkbook.GetComment(C9)
It says the formula I have entered contains an error. Thanks for your help.
 
OK. You pasted the code into "ThisWorkbook" code page. What you need to do is paste the code into a Module. On the VBA editor, click Insert/Module. That will open a new page. Past the code there, and delete the lines from "ThisWorkbook."

"ThisWorkbook" and "Sheet1" code pages allow you to handle events that can happen at the workbook and sheet levels respectively.
 
Being a non-VBA person (took the programming class several years ago and never used) how do I acomplish this in Excel?
 
From Excel, key Alt-F11 to open the VBA editor.

In the VBA editor, select Insert/Module from the menu.

In the code page that was inserted, paste the code from my posting (everything in
Code:
blue
).

Close the VBA editor.

From Excel, assuming the cell with a comment is in A1, enter the formula in B1 (or wherever):
Code:
B1: =GetComment(A1)
Copy down as far as needed.
If you use the formula button on the toolbar, you will find the function under User Defined Functions.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top