create comment from adjacent cell contents

[lross]

Hi,
I am trying to create a comment from the cell contents in an adjacent column (offset -1) for a range. The purpose is to create a popup comment that represents a part description when you hover over the part.
[TABLE="width: 184"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Part Description[/TD]
[/TR]
[TR]
[TD]part 1[/TD]
[TD]part 1 desc[/TD]
[/TR]
[TR]
[TD]part 2[/TD]
[TD]part 2 desc[/TD]
[/TR]
[TR]
[TD]part 3[/TD]
[TD]part 3 desc[/TD]
[/TR]
</tbody>[/TABLE]

The part descriptions are regularly refreshed and new parts (rows) added so the range should be row 3 to lastrow

Thanks

 

[Peter_SSs]

Try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

You didn't mention what columns this data is in. I have assumed columns D:E. Hopefully you can adapt if they are a different pair of columns.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Range("D:E"), Rows("3:" & Rows.Count))
  If Not Changed Is Nothing Then
    For Each c In Intersect(Changed.EntireRow, Columns("D"))
      c.ClearComments
      If Len(c.Offset(, 1).Text) Then c.AddComment.Text Text:=c.Offset(, 1).Text
    Next c
  End If
End Sub

 

[lross]

Hi Peter,
this basically works but would like to tweak to
1. Add the comment to the same cell in a different sheet
2. Autosize the comment box.
Tried

c.Comment.Shape.TextFrame.AutoSize = True

but gives a Method failure.

Cheers

 

[Peter_SSs]

1. Add the comment to the same cell in a different sheet

Some clarification please.
1. What sheet name and what column are the Part Numbers in?
2. What sheet name and what column are the Part Descriptions in?
3. What sheet name and what column do you want the comments in?

 

[lross]

Hi Peter
details as follows:
1. What sheet name and what column are the Part Numbers in? - Sheet = Pricelist, Column = B
2. What sheet name and what column are the Part Descriptions in? Sheet = Pricelist, Column = C
3. What sheet name and what column do you want the comments in? Sheet = Apps, Column = B

There is no row offset required.

Cheers

 

[Peter_SSs]

OK, try this Worksheet_Change code in the 'Pricelist' sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Columns("C"), Rows("3:" & Rows.Count))
  If Not Changed Is Nothing Then
    With Sheets("Apps")
      For Each c In Changed
        .Cells(c.Row, "B").ClearComments
        If Len(c.Text) Then
          .Cells(c.Row, "B").AddComment.Text Text:=c.Text
          .Cells(c.Row, "B").Comment.Shape.TextFrame.AutoSize = True
        End If
      Next c
    End With
  End If
End Sub

 

[lross]

Hi Peter,
perfect for the comment creation but the autosize gives"application defined or object defined error"

.Cells(c.Row, "B").Comment.Shape.TextFrame.AutoSize = True

Any ideas welcome

 

[Peter_SSs]

.. the autosize gives"application defined or object defined error"

Worked fine for me.
- What version of Excel are you using?
- What operating system?

 

[lross]

Hi Peter,
OS = Mac OS 10.14.6
Excel = 16.16.10

It looks like this may be a read-only property in Excel 16
https://stackoverflow.com/questions...xcel-2011-for-mac-not-working-in-ms-excel-201

Is there a way to test the version and only execute if not Excel 16 (other users have different versions)

Regards

 

[Peter_SSs]

OS = Mac OS 10.14.6

Sorry, I don't have a Mac Excel version (or even a Mac) & I suspect the problem relates to that as the autosize is working fine for me with Windows and Excel through Office 365.
Perhaps a Mac user will come along to assist further. Good luck!

Also, for any future questions that you might have I would suggest that it is best to state that you are using a Mac as although there are quite a few Mac users among the regular helpers here, the majority would be Windows-based users.