Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion
Thanks for your time and thoughts.
Interesting. The column change could be any column. I the following works but I believe that it is not full proof since " :" can be in a Row Address i.e. Range(1:1).EntireRow.Insert Is there another logical test Column Insertion? If Not InStr(1, Target.Address, ":") 0 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 Cells(1, IndexColumn + 1).Value = "Date Last Update" End If 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desire ChangeEvent not triggered if ChangeEvent was column (field)insertion
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blocking column insertion | Excel Programming | |||
Custom Context menu (Right click menu) not working in sheet changeevent. | Excel Programming | |||
ChangeEvent | Excel Programming | |||
Create event triggered by Pivot Chart field button | Excel Programming | |||
Text following an Outlook field insertion | Excel Programming |