Cell value into a Comment?
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Cell value into a Comment?

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?


    ~Anne Troy

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    449
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Using This Macro How Do I resize the comment box

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

  7. #7
    Board Regular
    Join Date
    Apr 2007
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  8. #8
    Board Regular
    Join Date
    Apr 2007
    Posts
    397
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  9. #9
    Board Regular
    Join Date
    Feb 2006
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com