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