Inserting Comments in Excel through VBA

[fahadakbar]

[TABLE="width: 512"]
<tbody>[TR]
[TD="colspan: 3"]I have a question[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]I want a comment at A1 which will have text that is in cell B1 , B2 and B3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]e.g B1, B2, & B3 has A , B & C in there [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]I want the macro to insert a comment in A1 with the text A,B,C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]any way to do that ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="8"></colgroup>[/TABLE]

 

[fahadakbar]

infect I want the comment to have the text that appears in every cell under column B

 

[scott_n_phnx]

For the first request, something like this should work.

Sub AddComment()
With Range("A1")
    .AddComment (Range("B1").Value & "," & Range("B2").Value & "," & Range("B3").Value)
End With
End Sub

 

[fahadakbar]

yes it works... but these are not the only cells having text... B5 or B7 can also have text, and I want them to be included as well ... just like an ongoing thing

 

[scott_n_phnx]

For including all text in column B, you can use this.

Sub AddComment()
Dim LastRow As Long
Dim c As Range
Dim strText
LastRow = Cells(Rows.count, 1).End(xlUp).row

For Each c In Range("B2", "B" & LastRow)
    strText = strText & c.Text & vbCrLf
Next c
With Range("A1").AddComment(strText)
    .Shape.TextFrame.AutoSize = True
End With
    Range("A1").Select

End Sub

The problem is, if you have a blank cell, it will be blank on the comment, but it might not be a big issue.