count comments funtion

  • Thread starter Thread starter riccifs
  • Start date Start date
R

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.
 
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,

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
Click to expand...

Hi Mike,
now it works very well!

Many many thanks for the great help you give to me,
Bye, Stefano.
 
Hi Mike,
now it works very well!

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

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.
 
Hi Mike,
now it works very well!

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

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.
 
Hi Mike,
now it works very well!

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

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.
 
Hi,

Tap F9

Mike
Click to expand...

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.
 
Hi Mike,
now it works very well!

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

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.
 
Hi Mike,
now it works very well!

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

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.
 
Hi Mike,
now it works very well!

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

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.
 
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.
Click to expand...

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
 
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
Click to expand...

Hi Rick,
many thanks the same for help!

Bye, Stefano.
 
Back
Top