Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding up COMMENTS | New Users to Excel | |||
Comments in protected cells with AutoFilter switched on | Excel Discussion (Misc queries) | |||
Adding Comments to a Cell | Excel Discussion (Misc queries) | |||
Adding comments | Charts and Charting in Excel | |||
Adding Comments | Excel Discussion (Misc queries) |