Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion
2003 2007
What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne |
Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion
Hi,
maybe this If Target.Column < 125 Then Exit Sub column 125 is "DU" so change to suit. Mike On Wed, 14 Oct 2009 08:22:50 -0400, wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne |
Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion
I thing I have it. The following works in that I am limiting the Date/Time to all changes where the cell count is 1. Anyone know where this is not appropriate or have a better approach? Set myRange = Range(Target.Address) If Target.Count = 1 Then For Each myCell In myRange With Me.Cells(myCell.Row, IndexColumn + 1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell End If TIA EagleOne wrote: Hi, maybe this If Target.Column < 125 Then Exit Sub column 125 is "DU" so change to suit. Mike On Wed, 14 Oct 2009 08:22:50 -0400, wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne |
Desire ChangeEvent not triggered if ChangeEvent was column (field)insertion
You can stop the rest of the code from running if the target is an entire column
(inserting or deleting!) with something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myTime As String If Target.Address = Target.EntireColumn.Address Then Exit Sub End If myTime = Now With Application .EnableEvents = False .ScreenUpdating = False End With For Each myCell In Target.Cells With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Me.Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub But if you insert or delete a column before column EA, then won't your tracking column move?. ========= If you want the column to be more "fluid", you could name a cell in that tracking column. Select the cell or entire column (DV) and use Insert|Name|define. Give it a nice sheet level name. Names in workbook: Sheet1!LastUpdateCol Refers to: =Sheet1!$DV$1 Then the code would change to something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myTime As String Dim myTrackCol As Range Set myTrackCol = Nothing On Error Resume Next Set myTrackCol = Me.Range("LastUpdateCol") On Error GoTo 0 If myTrackCol Is Nothing Then MsgBox "Design error!" & vbLf _ & "Please contact EagleOne at xxxx." Exit Sub End If If Target.Address = Target.EntireColumn.Address Then Exit Sub End If myTime = Now With Application .EnableEvents = False .ScreenUpdating = False End With For Each myCell In Target.Cells With Me.Cells(myCell.Row, myTrackCol.Column) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Me.Cells(1, myTrackCol.Column).Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne -- Dave Peterson |
Desire ChangeEvent not triggered if ChangeEvent was column (field)insertion
But if you insert or delete a column before column EA, then won't your tracking column move?. (I should have used DW -- not column EA.) Dave Peterson wrote: You can stop the rest of the code from running if the target is an entire column (inserting or deleting!) with something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myTime As String If Target.Address = Target.EntireColumn.Address Then Exit Sub End If myTime = Now With Application .EnableEvents = False .ScreenUpdating = False End With For Each myCell In Target.Cells With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Me.Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub But if you insert or delete a column before column EA, then won't your tracking column move?. ========= If you want the column to be more "fluid", you could name a cell in that tracking column. Select the cell or entire column (DV) and use Insert|Name|define. Give it a nice sheet level name. Names in workbook: Sheet1!LastUpdateCol Refers to: =Sheet1!$DV$1 Then the code would change to something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myTime As String Dim myTrackCol As Range Set myTrackCol = Nothing On Error Resume Next Set myTrackCol = Me.Range("LastUpdateCol") On Error GoTo 0 If myTrackCol Is Nothing Then MsgBox "Design error!" & vbLf _ & "Please contact EagleOne at xxxx." Exit Sub End If If Target.Address = Target.EntireColumn.Address Then Exit Sub End If myTime = Now With Application .EnableEvents = False .ScreenUpdating = False End With For Each myCell In Target.Cells With Me.Cells(myCell.Row, myTrackCol.Column) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Me.Cells(1, myTrackCol.Column).Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne -- Dave Peterson -- Dave Peterson |
Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion
Hi EagleOne
As Target is already a range you can just use Set MyRange =Target, or just juse Target in your macro. From what you post I can not tell if IndexColumn is supposed to be a fixed value or subject to change. As your code verify that Target is just one cell, you do not need a For Each...Next loop. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then Application.EnableEvents = False With Cells(Target.Row, IndexColumn+1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = Now() End With Application.EnableEvents = True End If End Sub Regards, Per skrev i meddelelsen ... I thing I have it. The following works in that I am limiting the Date/Time to all changes where the cell count is 1. Anyone know where this is not appropriate or have a better approach? Set myRange = Range(Target.Address) If Target.Count = 1 Then For Each myCell In myRange With Me.Cells(myCell.Row, IndexColumn + 1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell End If TIA EagleOne wrote: Hi, maybe this If Target.Column < 125 Then Exit Sub column 125 is "DU" so change to suit. Mike On Wed, 14 Oct 2009 08:22:50 -0400, wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne |
Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion
Hello Dave,
I had changed my code to: Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String Dim IndexColumn As Long myTime = Now() With ActiveSheet IndexColumn = .Cells.Find(What:="Index Key", After:=.Cells(1, 1), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column End With Set myRange = Range(Target.Address) Debug.Print Target.Count With Application .EnableEvents = False .ScreenUpdating = False End With If Target.Count = 1 Then For Each myCell In myRange With Me.Cells(myCell.Row, IndexColumn + 1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell End If Cells(1, IndexColumn + 1).Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub That said, I prefer your If clause. Thanks! Dave Peterson wrote: You can stop the rest of the code from running if the target is an entire column (inserting or deleting!) with something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myTime As String If Target.Address = Target.EntireColumn.Address Then Exit Sub End If myTime = Now With Application .EnableEvents = False .ScreenUpdating = False End With For Each myCell In Target.Cells With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Me.Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub But if you insert or delete a column before column EA, then won't your tracking column move?. ========= If you want the column to be more "fluid", you could name a cell in that tracking column. Select the cell or entire column (DV) and use Insert|Name|define. Give it a nice sheet level name. Names in workbook: Sheet1!LastUpdateCol Refers to: =Sheet1!$DV$1 Then the code would change to something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myTime As String Dim myTrackCol As Range Set myTrackCol = Nothing On Error Resume Next Set myTrackCol = Me.Range("LastUpdateCol") On Error GoTo 0 If myTrackCol Is Nothing Then MsgBox "Design error!" & vbLf _ & "Please contact EagleOne at xxxx." Exit Sub End If If Target.Address = Target.EntireColumn.Address Then Exit Sub End If myTime = Now With Application .EnableEvents = False .ScreenUpdating = False End With For Each myCell In Target.Cells With Me.Cells(myCell.Row, myTrackCol.Column) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Me.Cells(1, myTrackCol.Column).Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne |
Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion
Per,
Your point is very valid. (See the code in this thread to Dave Peterson) Thank you! "Per Jessen" wrote: Hi EagleOne As Target is already a range you can just use Set MyRange =Target, or just juse Target in your macro. From what you post I can not tell if IndexColumn is supposed to be a fixed value or subject to change. As your code verify that Target is just one cell, you do not need a For Each...Next loop. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then Application.EnableEvents = False With Cells(Target.Row, IndexColumn+1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = Now() End With Application.EnableEvents = True End If End Sub Regards, Per skrev i meddelelsen .. . I thing I have it. The following works in that I am limiting the Date/Time to all changes where the cell count is 1. Anyone know where this is not appropriate or have a better approach? Set myRange = Range(Target.Address) If Target.Count = 1 Then For Each myCell In myRange With Me.Cells(myCell.Row, IndexColumn + 1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell End If TIA EagleOne wrote: Hi, maybe this If Target.Column < 125 Then Exit Sub column 125 is "DU" so change to suit. Mike On Wed, 14 Oct 2009 08:22:50 -0400, wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne |
Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion
Eagle One,
If the heading 'Index Key' is changed or deleted for any reason, your code will not work. I suggest you insert a named range (Insert Name Define) in the worksheet named 'IndexColumn' and use this as reference in your macro. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then Application.EnableEvents = False DestCol = Range("IndexColumn").Column + 1 With Cells(Target.Row, DestCol) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = Now() End With Cells(1, DestCol).Value = "Date Last Update" Application.EnableEvents = True End If End Sub Regards, Per skrev i meddelelsen ... Hello Dave, I had changed my code to: Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String Dim IndexColumn As Long myTime = Now() With ActiveSheet IndexColumn = .Cells.Find(What:="Index Key", After:=.Cells(1, 1), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column End With Set myRange = Range(Target.Address) Debug.Print Target.Count With Application .EnableEvents = False .ScreenUpdating = False End With If Target.Count = 1 Then For Each myCell In myRange With Me.Cells(myCell.Row, IndexColumn + 1) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell End If Cells(1, IndexColumn + 1).Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub That said, I prefer your If clause. Thanks! Dave Peterson wrote: You can stop the rest of the code from running if the target is an entire column (inserting or deleting!) with something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myTime As String If Target.Address = Target.EntireColumn.Address Then Exit Sub End If myTime = Now With Application .EnableEvents = False .ScreenUpdating = False End With For Each myCell In Target.Cells With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Me.Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub But if you insert or delete a column before column EA, then won't your tracking column move?. ========= If you want the column to be more "fluid", you could name a cell in that tracking column. Select the cell or entire column (DV) and use Insert|Name|define. Give it a nice sheet level name. Names in workbook: Sheet1!LastUpdateCol Refers to: =Sheet1!$DV$1 Then the code would change to something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim myCell As Range Dim myTime As String Dim myTrackCol As Range Set myTrackCol = Nothing On Error Resume Next Set myTrackCol = Me.Range("LastUpdateCol") On Error GoTo 0 If myTrackCol Is Nothing Then MsgBox "Design error!" & vbLf _ & "Please contact EagleOne at xxxx." Exit Sub End If If Target.Address = Target.EntireColumn.Address Then Exit Sub End If myTime = Now With Application .EnableEvents = False .ScreenUpdating = False End With For Each myCell In Target.Cells With Me.Cells(myCell.Row, myTrackCol.Column) .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Me.Cells(1, myTrackCol.Column).Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End With End Sub wrote: 2003 2007 What would the VBA Syntax to eliminate (from ChangeEvent) i.e. the addition of a Column within the Target Range? The issue is that all cells in that column would be populated eith a time/date stamp. Private Sub Worksheet_Change(ByVal Target As Excel.Range) ' ' Dim myCell As Range Dim myRange As Range Dim myTime As String myTime = Now() Set myRange = Range(Target.Address) With Application .EnableEvents = False .Screenupdating = False End with For Each myCell In myRange With Me.Cells(myCell.Row, "DZ") .NumberFormat = "mm/dd/yyyy hh:mm:ss" .Value = myTime End With Next myCell Range("DZ1").Value = "Date Last Update" With Application .ScreenUpdating = True .EnableEvents = True End with End Sub TIA EagleOne |
Desire ChangeEvent not triggered if ChangeEvent was column (field)insertion
|
All times are GMT +1. The time now is 10:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com