Cell Comments

Deano

Active Member
Joined
May 2, 2004
Messages
264
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,813
Office Version
  1. 365
Platform
  1. Windows
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....
Code:
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

Active Member
Joined
Mar 13, 2004
Messages
371
If you wanted to avoid the button and always have B1 contain the comment from A1, you could change to a workbook change event:

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

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,813
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 1, 2002
Messages
1,813
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 1, 2002
Messages
1,813
Office Version
  1. 365
Platform
  1. Windows
Off to work now.
Will see if this has progressed when I get back later.....
 

Deano

Active Member
Joined
May 2, 2004
Messages
264
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
 

Forum statistics

Threads
1,136,654
Messages
5,677,012
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top