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

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


(E-Mail Removed) 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, (E-Mail Removed)
>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

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

(E-Mail Removed) 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, (E-Mail Removed)
>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

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





(E-Mail Removed) 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

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
>
> (E-Mail Removed) 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

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

<(E-Mail Removed)> skrev i meddelelsen
news:(E-Mail Removed)...
>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
>
> (E-Mail Removed) 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, (E-Mail Removed)
>>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

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 <(E-Mail Removed)> 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
>
>
>
>
>
>(E-Mail Removed) 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

Per,

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

Thank you!

"Per Jessen" <(E-Mail Removed)> 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
>
><(E-Mail Removed)> skrev i meddelelsen
>news:(E-Mail Removed)...
>>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
>>
>> (E-Mail Removed) 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, (E-Mail Removed)
>>>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

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

<(E-Mail Removed)> skrev i meddelelsen
news(E-Mail Removed)...
> 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 <(E-Mail Removed)> 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
>>
>>
>>
>>
>>
>>(E-Mail Removed) 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

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.



(E-Mail Removed) wrote:
>
> 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 <(E-Mail Removed)> 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
> >
> >
> >
> >
> >
> >(E-Mail Removed) 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