Updating Comments Dynamically From Cell Value in VB but gets truncated

[steveconnolly]

Hi,

Long time lurker, first time poster.

I'm am trying to populate the "comments" of a cell based on a combination of cells on another sheet (don't ask!).

Anyhow, let's assume we have SheetMain & SheetDesc. What I will want is for the comments on SheetMain to be taken from a number of specific cells on SheetDesc.

In the crudest form:

Selection.ClearComments

[A1].AddComment.Text "" & Format(Worksheets("SheetDesc").Range("A1")) & Chr(10) & Format(Worksheets("SheetDesc").Range("A2")) _
      & Chr(10) & Format(Worksheets("SheetDesc").Range("A3"))
      
       [A1].Comment.Shape.TextFrame.AutoSize = True

/CODE]

It works to a point - but what it is effectively doing is truncating the comments - but it is nowhere near the max o365 seems to specify (cuts randomly after pehaps 20 or 30 characters).

Is there a better way to concatenate and populate a comments box where the concatenation does not get truncated?

TIA. Steve.

 

[Swapnil Shah]

Try this

Sub Comment()
   With Sheets("SheetMain").Range("a1")
        .ClearComments
        .AddComment (Sheets("SheetDesc").Range("A1").Value & vbCrLf & Sheets("SheetDesc").Range("A2").Value & vbCrLf & Sheets("SheetDesc").Range("A3").Value)
        .Comment.Shape.TextFrame.AutoSize = True
    End With
    
End Sub

Thanks

Swapnil Shah

 

[steveconnolly]

Legend! Thank You