getting 1 line of text from a cell comment

[Kevin]

if a cell has a comment added that says:

Lineone
LineTwo

How can I have a msgbox that would only get the first line or second line of
that comment?

MsgBox ActiveCell.Comment.text??????

Thanks

 

[Rick Rothstein]

You can use the Split function to separate the lines. For example...

Dim CommentLines() As String
CommentLines = Split(Range("A1").Comment.Text, vbLf)
MsgBox "Line1 = " & CommentLines(0)
MsgBox "Line2 = " & CommentLines(1)

Note that Split **always** returns a zero-based array (hence, the 0 and 1
array indexes).

By the way, you do not have to declare an array and then assign the output
from the Split function to it... since Split returns an array, you can
address its elements directly...

MsgBox "Line1 = " & Split(Range("A1").Comment.Text, vbLf)(0)
MsgBox "Line2 = " & Split(Range("A1").Comment.Text, vbLf)(1)

Of course, if you did not know how many lines there were, you would have to
use the array method and a loop...

Dim X As Long
Dim CommentLines() As String
CommentLines = Split(Range("A1").Comment.Text, vbLf)
For X = 0 To UBound(CommentLines)
MsgBox "Line " & (X + 1) & " = " & CommentLines(X)
Next

 

[Kevin]

Fantastic, Thanks so much!!!

You can use the Split function to separate the lines. For example...

Dim CommentLines() As String
CommentLines = Split(Range("A1").Comment.Text, vbLf)
MsgBox "Line1 = " & CommentLines(0)
MsgBox "Line2 = " & CommentLines(1)

Note that Split **always** returns a zero-based array (hence, the 0 and 1
array indexes).

By the way, you do not have to declare an array and then assign the output
from the Split function to it... since Split returns an array, you can
address its elements directly...

MsgBox "Line1 = " & Split(Range("A1").Comment.Text, vbLf)(0)
MsgBox "Line2 = " & Split(Range("A1").Comment.Text, vbLf)(1)

Of course, if you did not know how many lines there were, you would have to
use the array method and a loop...

Dim X As Long
Dim CommentLines() As String
CommentLines = Split(Range("A1").Comment.Text, vbLf)
For X = 0 To UBound(CommentLines)
MsgBox "Line " & (X + 1) & " = " & CommentLines(X)
Next