Solved: Retrieving comments from a worksheet

[simora]

I am attempting to display the comments from a worksheet and show them on a userform in Textbox1.

If there's a comment in the cell that I reference, everything works fine however, if there's no cell comment, I get an error;

Run-time error 91
Object variable or With block data not set

Here is the code:

 
Dim CommentData As Range
Set CommentData = SourceRange.Offset(ListBox1.ListIndex, 17).Resize(1, 1)
If CommentData.Comment.Text <> "" Then
                    TextBox1.Value = SourceRange.Offset(ListBox1.ListIndex, 17).Resize(1, 1).Comment.Text
                End If

Can someone please tell me why I'm getting this error and how to fix it. I've tried most variations of If Not senarios, and tried Selecting the cell and using the With construction.

Appreciate your help.

Thanks

[Paul_Hossler]

What I usually do is something like this

[VBA]
Sub drv()
Dim oComment As Comment

Set oComment = Range("A1").Comment
If oComment Is Nothing Then
MsgBox "No Comment"
Else
MsgBox oComment.Text
End If

Set oComment = Range("A2").Comment
If oComment Is Nothing Then
MsgBox "No Comment"
Else
MsgBox oComment.Text
End If

End Sub
[/VBA]

Paul

[simora]

Thanks Paul:

Great! You pointed me in the right direction.