Cell Comments

[Deano]

Hi

Is there some way that I can put a formula in say cell b1 that will pull out the comments in cell a1 which have been types into the comment field and not into the actual cell?

Regards

 

[sykes]

Deano

Some of the other guys and girls are tip top with formulae, but it's not my strong point so I'm not sure about a formula solution, as you've asked.
However, if you were interested in a starter towards a VBA solution, you could put a command button on the sheet in question, and put this code in the button's click event....

Me.Range("B1").Value = Me.Range("a1").Comment.Text

...clicking the button puts the comment's text into B1 as you've asked.

You could then experiment with the best way to fire the code (not necessarily using the button) in order to get the text into the cells when you wished.

 

[chicagodiceman]

If you wanted to avoid the button and always have B1 contain the comment from A1, you could change to a workbook change event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("B1").Value = Range("a1").Comment.Text
End Sub

 

[sykes]

Yes, you read my mind - I was going to start down that road if Deano was OK with a code solution , but I was holding fire for a response first.

 

[sykes]

Hi Erik, nice to see you.....

Yes, that would be fine wouldn't it?

Now, you're a top Mr. Excel bloke - any thoughts on a formula solution as the OP fist requested?

 

[sykes]

Off to work now.
Will see if this has progressed when I get back later.....

 

[Deano]

Thanks guys, your enthusiastic response is much appreciated.
Not having much "code" experience, I'm still hoping for a formula solution.

I did, however try the code that Sykes suggested and it works, but if I have a big worksheet and want to extract the comments from many rows, I would have to start using what Chicagodiceman suggested.

This would still be fairly fixed and not as copyable as a formula.

Thanks and Regards