getting 1 line of text from a cell comment
 

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

getting 1 line of text from a cell comment
 

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

--
Rick (MVP - Excel)


"Kevin" wrote in message
...
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

getting 1 line of text from a cell comment
 

Fantastic, Thanks so much!!!

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Kevin" wrote in message
...
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