Reading Comments

[AlexanderBB]

I'd like to read any comment in my worksheet. I'm going through row by row (my variable i) with code

For Pos = 1 To 10
      CR = colLetter(Pos)
       If .Range(CR & i).Comment.Text > "" Then
       Stop
       End If
Next
Function colLetter(intNumber) As String
    If Val(intNumber) = 0 Then
        colLetter = ""
    Else
        colLetter = Left(Cells(1, Val(intNumber)).Address(0, 0), 2 + (Val(intNumber) <= 26))
    End If
End Function

If there's no comment I have to trap this error
Run-time error '91':
Object variable or With block variable not set

Is this an OK approach, or perhaps a better method exists ?
Thanks.

 

[Jaafar Tribak]

Something like this :

Dim cmnt As Comment
For Each cmnt In Sheet1.Comments
    Debug.Print cmnt.Text
Next cmnt

Change Sheet1 to suit.

 

[AlexanderBB]

Thanks. That seems a really good approach, but is there any way to find the cmnt.text.address ?

 

[Jaafar Tribak]

Thanks. That seems a really good approach, but is there any way to find the cmnt.text.address ?

Do you mean the address of the cell that has the coment ?

 

[AlexanderBB]

Do you mean the address of the cell that has the coment ?

Yes please. I have all the comments in a database and want to confirm all are present and correct in Excel.

 

[Jaafar Tribak]

This should read the text of all comments in Sheet1 along with their cells addresses :

Dim cmnt As Comment
For Each cmnt In Sheet1.Comments
    Debug.Print "Cell Address : " & cmnt.Parent.Address
    Debug.Print "Comment Text : " & cmnt.Text & vbLf & "___________________" & vbLf
Next cmnt

 

[AlexanderBB]

Great.. thanks very much.

 

[Jaafar Tribak]

Great.. thanks very much.

You are welcome. If you need any more assistance, just ask.

 

[AlexanderBB]

I'm getting error Assignment to constant not permitted if try to assign or edit cmnt.text.
You may have a good way to get around that, mine is a bit overkill. But I do want to keep the same formatting. shape that exists

 

[Jaafar Tribak]

I'm getting error Assignment to constant not permitted if try to assign or edit cmnt.text.
You may have a good way to get around that, mine is a bit overkill. But I do want to keep the same formatting. shape that exists

Can you post the code.