Desire ChangeEvent not triggered if ChangeEvent was column (field) insertion

[EagleOne]

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

 

[EagleOne]

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 re: 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

 

[EagleOne]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

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.)

 

[Per Jessen]

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

 

[EagleOne]

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!

 

[EagleOne]

Per,

Your point is very valid. (See the code in this thread to Dave Peterson)

Thank you!

 

[Per Jessen]

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

 

[Dave Peterson]

There's no reason to use:

Set myRange = Range(Target.Address)

And don't use ActiveSheet. Use Me. That keyword represents the object owning
the code. In this case, the worksheet that just got changed.