Cell value into a Comment?

[chuck]

I've seen various posts on extracting comments to cells. I need the other way around.

Col A is filled with part numbers.
Col B has the corresponding descriptions.

I'd like to take the descriptions in B
and turn them into comments for A.

It is a lot easier create comment text in a cell than in the comment box itself.

Ideas? Macros? Flames?
Thx, chuck

[Anne Troy]

Flame: I can't figure out why in the world you would want to do that. Perhaps there's something you're trying to accomplish that would be more easily accomplished using Excel's features?

[chuck]

The descriptions in Col B can be fairly lengthy and I didn't want them cluttering up the sheet. I had planned on hiding Col B once the descriptions when into the comment.

Your reply made me think about it a bit more (which was probably the purpose). I suppose I could move the descriptions to another sheet in the workbook and turn the item numbers in Col A into Hyperlinks.

If you had a different Excel feature in mind, I'm open to ideas.

chuck

[Ivan F Moala]

On 2002-03-27 16:52, chuck wrote:
The descriptions in Col B can be fairly lengthy and I didn't want them cluttering up the sheet. I had planned on hiding Col B once the descriptions when into the comment.

Your reply made me think about it a bit more (which was probably the purpose). I suppose I could move the descriptions to another sheet in the workbook and turn the item numbers in Col A into Hyperlinks.

If you had a different Excel feature in mind, I'm open to ideas.

chuck

Hi Chuck
You could try this code;
Assumes
1)Part numbers Start A1
2)Discrp in Start B1
3)No blanks in your part number column

What it does:
1) gets column A part range
2) loops through this and deletes any comment
then adds the comment from column B
3) deletes Discrp in column B

Suggest you make a copy of these discriptions
so that if you need to change them just amend
the discp...paste and run macro again.

Amend as required.


Sub InsertComm()
Dim RgPartnumb As Range
Dim NRg As Range

Set RgPartnumb = Range(Range("A1"), Range("A1").End(xlDown))
On Error Resume Next
For Each NRg In RgPartnumb
With NRg
.Comment.Delete
.AddComment Text:=NRg.Offset(0, 1).Text
.Offset(0, 1).ClearContents
End With
Next

End Sub


HTH

Ivan

[chuck]

Ivan,
Thank you. It works perfectly.
Just what I needed.
Chuck

[nehpets12]

Using This Macro How Do I resize the comment box

Selection.ShapeRange.ScaleWidth 1.82, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 2.51, msoFalse, msoScaleFromTopLeft

[Thebatfink]

Sorry to resurect such an old post but its exactly what I am trying to do. I want to put the result of a formula in a cell into a cell column.

The cells I want into comments are AH7:AH46, and I would like them in comments on cells E7:E46.

How would I modify the code to put in specific ranges?

Cheers!

[Thebatfink]

I've gotten it to work with the following code

Sub InsertComm()
Dim RgPartnumb As Range
Dim NRg As Range

Set RgPartnumb = Range("E7:E46")
On Error Resume Next
For Each NRg In RgPartnumb
With NRg
.Comment.Delete
.AddComment Text:=NRg.Offset(0, 29).Text
End With
Next

End Sub

Now, this works pretty much as intended, however, how do I make it automatically update the comment whenever the value in column AH is changed?

Also I would like to change the size of the comment too, I have tried this code and whilst is does not give any errors when I jog through it, it doesnt seem to alter the size of the comment box?

Sub InsertComm()
Dim RgPartnumb As Range
Dim NRg As Range

Set RgPartnumb = Range("E7:E46")
On Error Resume Next
For Each NRg In RgPartnumb
With NRg
.Comment.Delete
.AddComment Text:=NRg.Offset(0, 29).Text
.Comment.Shape.Select True
.Selection.ShapeRange.ScaleWidth 1.01, msoFalse, msoScaleFromTopLeft
.Selection.ShapeRange.ScaleHeight 0.93, msoFalse, msoScaleFromTopLeft
End With
Next

End Sub

Anyone have any ideas on these couple of things?

Thanks!

[wildturkey]

My first solution to someone elses problem even though its the result of mixing code from 2 other responses on these forums Im still chuffed.

Sub auto_open()
Dim RgPartnumb As Range
Dim NRg As Range

Set RgPartnumb = Range(Range("P1"), Range("P1").End(xlDown))
On Error Resume Next
For Each NRg In RgPartnumb
With NRg
.Comment.Delete
.AddComment Text:=NRg.Offset(0, 0).Text

End With
With NRg.Comment.Shape
.AutoShapeType = msoShapeRoundedRectangle
.Shadow.OffsetX = 20
.Shadow.OffsetY = 20
.Shadow.Transparency = 0.4
.Height = 150
.Width = 175
.IncrementLeft -150
End With
Next

End Sub