Referencing Cell Comments in Excel 1

[AngelB]

Hello All

I am using VBA in Excel 2003 (SP2).

I would like to set the value of a variable depending on whether or not a cell has a comment in it. If the cell has no comment, the variable is set to the cell value. If the cell does have a comment, the variable should be set equal to the comment text. I am aware that you cannot reference comment text of a cell if it does not have a comment, so can anyone suggest how I can reword the following code so that it doesn't throw an error?

If Range("E1").Comment.Text = "" Then
     MyVar = Range("E1").Value
Else
     MyVar = Range("E1").Comment.Text
End If

Many Thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[combo]

You need another test:
If Range("E1").Comment Is Nothing Then
....
ElseIf Range("E1").Comment.Text = ""
....
Else
....
End If

combo

 

[AngelB]

Thank you combo, that was the test I needed. Code runs great now.

Many Thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy