Returning the number of comments in a workbook
 

I've figured out how to do it in a Word document, but the object model or
something must be different for Excel (2003).

How can I have a msgbox pop up and tell me how many comments are in a
workbook?

thank you!

Returning the number of comments in a workbook
 

try something like

Sub test()
Dim lCommentCount As Long
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
lCommentCount = lCommentCount + sht.Comments.Count
Next sht
MsgBox lCommentCount & " total comments in the active workbook"
End Sub


--
Hope that helps.

Vergel Adriano


"Sue" wrote:

I've figured out how to do it in a Word document, but the object model or
something must be different for Excel (2003).

How can I have a msgbox pop up and tell me how many comments are in a
workbook?

thank you!

Returning the number of comments in a workbook
 

In a worksheet:

MsgBox Cells.SpecialCells(xlCellTypeComments).Count


In a multi-sheet workbook:

Dim myComment As Integer
Dim mySht As Worksheet
On Error Resume Next
For Each mySht In Worksheets
myComment = myComment + mySht.Cells.SpecialCells(xlCellTypeComments).Count
Next mySht

MsgBox myComment

HTH,
Bernie
MS Excel MVP


"Sue" wrote in message
...
I've figured out how to do it in a Word document, but the object model or
something must be different for Excel (2003).

How can I have a msgbox pop up and tell me how many comments are in a
workbook?

thank you!

Returning the number of comments in a workbook
 

Excellent! thank you!!!

"Vergel Adriano" wrote:

try something like

Sub test()
Dim lCommentCount As Long
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
lCommentCount = lCommentCount + sht.Comments.Count
Next sht
MsgBox lCommentCount & " total comments in the active workbook"
End Sub


--
Hope that helps.

Vergel Adriano


"Sue" wrote:

I've figured out how to do it in a Word document, but the object model or
something must be different for Excel (2003).

How can I have a msgbox pop up and tell me how many comments are in a
workbook?

thank you!

Returning the number of comments in a workbook
 

sub ABC()
Dim sh as worksheet
Dim cnt as Long
for each sh in Worksheets
cnt = sh.Comments.count + cnt
Next
msgbox "Comment count is " & cnt
End Sub

--
Regards,
Tom Ogilvy



"Sue" wrote:

I've figured out how to do it in a Word document, but the object model or
something must be different for Excel (2003).

How can I have a msgbox pop up and tell me how many comments are in a
workbook?

thank you!

Returning the number of comments in a workbook
 

Thank you Bernie, this is very helpful

"Bernie Deitrick" wrote:

In a worksheet:

MsgBox Cells.SpecialCells(xlCellTypeComments).Count


In a multi-sheet workbook:

Dim myComment As Integer
Dim mySht As Worksheet
On Error Resume Next
For Each mySht In Worksheets
myComment = myComment + mySht.Cells.SpecialCells(xlCellTypeComments).Count
Next mySht

MsgBox myComment

HTH,
Bernie
MS Excel MVP


"Sue" wrote in message
...
I've figured out how to do it in a Word document, but the object model or
something must be different for Excel (2003).

How can I have a msgbox pop up and tell me how many comments are in a
workbook?

thank you!