getting 1 line of text from a cell comment

  • Thread starter Thread starter Kevin
  • Start date Start date
K

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
 
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
 
Fantastic, Thanks so much!!!

Rick Rothstein said:
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
Click to expand...
 
Back
Top