autofilter and adding comments
 

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

autofilter and adding comments
 

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

--
Regards,
Tom Ogilvy

"Valeria" wrote in message
...
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

autofilter and adding comments
 

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" wrote:

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

--
Regards,
Tom Ogilvy

"Valeria" wrote in message
...
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

autofilter and adding comments
 

my fault

if cell.EntireRow.Visible = True then

should be

if cell.EntireRow.Hidden = False then

--
Regards,
Tom Ogilvy


"Valeria" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"Valeria" wrote in message
...
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

autofilter and adding comments
 

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

"Tom Ogilvy" wrote:

my fault

if cell.EntireRow.Visible = True then

should be

if cell.EntireRow.Hidden = False then

--
Regards,
Tom Ogilvy


"Valeria" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"Valeria" wrote in message
...
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