count comments funtion

[riccifs]

Hi to everyone,
does anyone to know how to build a function, not a macro, to count the
number of comments in a cells range?

searching to this news group I found this code:

Public Function CommentCount()
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In Selection
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

but it doesn't seem to work for me. Can someone help me?

I really do appreciate who will give to me an hand
Bye,Stefano.

 

[Mike H]

Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

 

[riccifs]

Hi,

Sending a range to it looks simpler
call with
=CommentCount(A1:a100)
Not it must be inserted in a module

Public Function CommentCount(MyRange As Range)
Dim cell As Range
Dim cnt As Long
Dim cmt As Comment
Application.Volatile
On Error Resume Next
For Each cell In MyRange
Set cmt = Nothing
Set cmt = cell.Comment
If Not cmt Is Nothing Then
cnt = cnt + 1
End If
Next cell
CommentCount = cnt
End Function

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

 

[riccifs]

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

 

[riccifs]

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

 

[riccifs]

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

 

[Mike H]

Hi,

Tap F9

Mike

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

 

[riccifs]

Hi,

Tap F9

Mike

Hi Mike,
I'd like to make it automatic when I add and/or when I delete a
comment
I tried with "Application.Volatile True" but it does not work.

Bye, Stefano.

P.S.
sorry for the double previous post, I make I mistake.

 

[riccifs]

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

 

[riccifs]

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

 

[riccifs]

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.

Hi Mike,
let me ask to you just a last thing.

Do you know how to automatic update that formula?
I mean, without hit enter every time, on the cell where I insert the
function.

Bye, Stefano.

 

[Rick Rothstein \(MVP - VB\)]

I'd like to make it automatic when I add and/or when I delete a

comment
I tried with "Application.Volatile True" but it does not work.

I don't have an answer as to how to do this (it appears that adding and deleting comments doesn't generate any events); however, unless I missed something, this function appears to do what your originally posted code does, but is a little bit shorter...

Public Function CommentCount(MyRange As Range)
Dim cell As Range
On Error Resume Next
For Each cell In MyRange
CommentCount = CommentCount - (cell.Comment.Text <> "")
Next cell
End Function

Rick

 

[riccifs]

I don't have an answer as to how to do this (it appears that adding and deleting comments doesn't generate any events); however, unless I missed something, this function appears to do what your originally posted code does, but is a little bit shorter...

Public Function CommentCount(MyRange As Range)
Dim cell As Range
On Error Resume Next
For Each cell In MyRange
CommentCount = CommentCount - (cell.Comment.Text <> "")
Next cell
End Function

Rick

Hi Rick,
many thanks the same for help!

Bye, Stefano.