autofilter and adding comments

[Guest]

Dear experts,
I have a code that, whenever you change a cell in a worksheet, adds a
comment with the username and date on that cell.
Now, if you drag the cell where you have made the input to copy it on other
cells, and the autofilter is on, the code does not make the difference
between filtered and non filtered cells!
That's to say, if you just have rows 2 and 5 autofiltered and drag the cell
from row 2 to 5, the cell content will be correctly copied, but the comments
will be added in every cell from row 2 to 5.

Is there any way I can modify this behaviour?
Many thanks in advance for your help,
Best regards,
Valeria

My code is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vvvrange As Range
Dim cell As Object
Set vvvrange = Range("Comment_Input") '$T$1:$W$400
Application.EnableEvents = False
On Error Resume Next
For Each cell In Target
If Union(cell, vvvrange).Address = vvvrange.Address Then
cell.Comment.Delete
cell.AddComment
cell.Comment.Visible = False
cell.Comment.Text Text:=Application.UserName & Chr(10) & Format(Date,
"DD-MMM-YYYY")
cell.Comment.Shape.TextFrame.AutoSize = True
End If
Next cell
On Error GoTo 0
Application.EnableEvents = True
End Sub

 

[Tom Ogilvy]

Possibly:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vvvrange As Range
Dim cell As Object
Set vvvrange = Range("Comment_Input") '$T$1:$W$400
Application.EnableEvents = False
On Error Resume Next
For Each cell In Target
If Union(cell, vvvrange).Address = vvvrange.Address Then
if cell.EntireRow.Visible = True then
cell.Comment.Delete
cell.AddComment
cell.Comment.Visible = False
cell.Comment.Text Text:=Application.UserName & _
Chr(10) & Format(Date, "DD-MMM-YYYY")
cell.Comment.Shape.TextFrame.AutoSize = True
end if
End If
Next cell
On Error GoTo 0
Application.EnableEvents = True
End Sub

 

[Guest]

Hi Tom,
I get the warning in the watch window that <Object doesn't support this
property or method>, and Excel ignores the line...
What should I change?
Many thanks!
Best regards,
Valeria

 

[Tom Ogilvy]

my fault

if cell.EntireRow.Visible = True then

should be

if cell.EntireRow.Hidden = False then

 

[Guest]

Hi Tom,
it works wonderfully, thank you very much!
Best regards,
Valeria