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


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


Paul

[simora]

Thanks Paul:

Great! You pointed me in the right direction.