Hello,
How can I tell if a cell has a comment in it?

Thanks,

Bill

There are options to view them under Tools--Options--View. If you turn on
these options, you can see a) a mark on the corner of cells with comments,
or b) the entire comment shows.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Bill" wrote in message
k.net...
Hello,
How can I tell if a cell has a comment in it?

Thanks,

Bill

Sorry, I meant how to determine that using VBA.


"Anne Troy" wrote in message
news:685c5$42c1721d$97c5108d$19194@allthenewsgroup s.com...
There are options to view them under Tools--Options--View. If you turn
on
these options, you can see a) a mark on the corner of cells with comments,
or b) the entire comment shows.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Bill" wrote in message
k.net...
Hello,
How can I tell if a cell has a comment in it?

Thanks,

Bill

I think something like that should work...

CommentString = "The Text You want to enter as a comment"
With CellToComment
If .Comment Is Nothing Then 'test if there is already a comment in the
cell
.AddComment CommentString
Else
.Comment.Text CommentString
End If
End With

Alain79 from France

"Bill" wrote in message
. net...
Sorry, I meant how to determine that using VBA.


"Anne Troy" wrote in message
news:685c5$42c1721d$97c5108d$19194@allthenewsgroup s.com...
There are options to view them under Tools--Options--View. If you turn
on
these options, you can see a) a mark on the corner of cells with
comments,
or b) the entire comment shows.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Bill" wrote in message
k.net...
Hello,
How can I tell if a cell has a comment in it?

Thanks,

Bill

Yes, that is good. I used to use code which trapped for an error til I
learned your way, Alain, from Aaron Blood.

Dim Dummy
On Error Resume Next
Dummy = Range("A1").Comment.Text
If Err.Number = 91 Then 'No comment is there
'Do something
Else
'Do something else
End If
On Error GoTo 0

"Alain79" wrote in message
...
I think something like that should work...

CommentString = "The Text You want to enter as a comment"
With CellToComment
If .Comment Is Nothing Then 'test if there is already a comment in the
cell
.AddComment CommentString
Else
.Comment.Text CommentString
End If
End With

Alain79 from France

"Bill" wrote in message
. net...
Sorry, I meant how to determine that using VBA.


"Anne Troy" wrote in message
news:685c5$42c1721d$97c5108d$19194@allthenewsgroup s.com...
There are options to view them under Tools--Options--View. If you
turn
on
these options, you can see a) a mark on the corner of cells with
comments,
or b) the entire comment shows.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Bill" wrote in message
k.net...
Hello,
How can I tell if a cell has a comment in it?

Thanks,

Bill

Just and added thought:

Using the older object model makes working with comments trivial

if activecell.NoteText = vbNullString then
' no comment

It is also a great way to create a comment or modify a comment without
getting into errors and so forth.

If you want a list of cells with comments, then use the comments collection


for each cmt in activesheet.comments
msgbox cmt.parent.address
Next

--
Regards,
Tom Ogilvy



"Bill" wrote in message
. net...
Sorry, I meant how to determine that using VBA.


"Anne Troy" wrote in message
news:685c5$42c1721d$97c5108d$19194@allthenewsgroup s.com...
There are options to view them under Tools--Options--View. If you turn
on
these options, you can see a) a mark on the corner of cells with
comments,
or b) the entire comment shows.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Bill" wrote in message
k.net...
Hello,
How can I tell if a cell has a comment in it?

Thanks,

Bill

Edit Menu - Goto - Special - select comments.
If you have any they will be selected

Yes, menu can select comments on the active sheet. If you want to know
whether a cenn has a comment anywhere in the workbook, without changing the
activesheet.

Dim W As Workbook
Dim S As Worksheet
Dim C As Range
Set W = ThisWorkbook
Dim strCommentText As String

For Each S In W.Worksheets
For Each C In S.UsedRange
If Not (C.Comment Is Nothing) Then
strCommentText = C.Comment.Text
'I like to remove the first break in lines
strCommentText = Replace(C.Comment.Text, Chr(10), " ")

Debug.Print S.Name & "!" & C.AddressLocal(False, False) & "
comment: " & _
strCommentText
End If
Next C
Next S


wrote in message
ups.com...
Edit Menu - Goto - Special - select comments.
If you have any they will be selected

More condensed and using Tom Ogilvy's approach:

Dim S As Worksheet
Dim C As comment
Dim strCommentText As String

For Each S In ThisWorkbook.Worksheets
For Each C In S.Comments
strCommentText = C.Text
'I like to
remove the first break in lines
strCommentText = Replace(strCommentText, Chr(10), " ")
Debug.Print S.Name & "!" & C.Parent.Address(False, False) & "
comment: " & strCommentText
Next C
Next S
End Sub


"William Benson" wrote in message
...
Yes, menu can select comments on the active sheet. If you want to know
whether a cenn has a comment anywhere in the workbook, without changing
the activesheet.

Dim W As Workbook
Dim S As Worksheet
Dim C As Range
Set W = ThisWorkbook
Dim strCommentText As String

For Each S In W.Worksheets
For Each C In S.UsedRange
If Not (C.Comment Is Nothing) Then
strCommentText = C.Comment.Text
'I like to remove the first break in lines
strCommentText = Replace(C.Comment.Text, Chr(10), " ")

Debug.Print S.Name & "!" & C.AddressLocal(False, False) & "
comment: " & _
strCommentText
End If
Next C
Next S


wrote in message
ups.com...
Edit Menu - Goto - Special - select comments.
If you have any they will be selected