Worksheet Change Event - track changes

[karen]

I'm looking for an easy way to track changes in a large spreadsheet
Could someone help me with code to add a date into column P if any changes
are made in the row. The range that this needs to apply to are is A2:N5000

I know this is a worksheet change event but don't understand the mechanics
of writing the code to do what I want

 

[Jacob Skaria]

Hi Karen

Try the below which makes use of Worksheet Change and Selection events to
track real changes made to the worksheet. Select the sheet tab which you want
to work with. Right click the sheet tab and click on 'View Code'. This will
launch VBE. Paste the below code to the right blank portion. Get back to to
workbook and try out.


Dim varData As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2:N5000")) Is Nothing Then
If Target.Count = 1 And Target.Value <> varData Then
Application.EnableEvents = False
Range("P" & Target.Row) = Now()
Application.EnableEvents = True
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varData = Target.Value
End Sub

 

[marcus]

Hi Karen

Try this out. It puts a date in P1 when the cells in your stated
range change. Put it in the worksheet object you want it to run from.

Take care

Marcus


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:N5000")) Is Nothing Then
Range("P1").Value = Now()
End If

End Sub

 

[Patrick Molloy]

right click the sheet tab and select view code, then paste the following

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:N5000")) Is Nothing Then
Cells(Rows.Count, "P").End(xlUp).Offset(1) = Date
End If
End Sub

the Intersect returns a range object containing the range of cells if two or
more ranges overlap. It returns nothing if there's no overlap. So we test
NOT is Nothing ie, there the cell changed is in the range.
If so, then we go to the bottom of the column P, end/up to the last used
cell, offset one row down, which will be the next empty cell, and save
today's date. This will of course fire the change event again, but as P is
outside your check area, it wont do anything.

 

[Karen Mckenzie]

I'm looking for an easy way to track changes in a large spreadsheet
Could someone help me with code to add a date into column P if any
changes
are made in the row. The range that this needs to apply to are is
A2:N5000

I know this is a worksheet change event but don't understand the
mechanics
of writing the code to do what I want

Perfect, Thanks!!!

 

[Karen Mckenzie]

Hi Karen

Try this out. It puts a date in P1 when the cells in your stated
range change. Put it in the worksheet object you want it to run from.

Take care

Marcus


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:N5000")) Is Nothing Then
Range("P1").Value = Now()
End If

End Sub

Worked perfectly, Thanks!!

 

[Dave Peterson]

One more:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myCell As Range
Dim myRngToInspect As Range
Dim myIntersect As Range

Set myRngToInspect = Me.Range("A2:N5000")

Set myIntersect = Intersect(Target, myRngToInspect)

If myIntersect Is Nothing Then
Exit Sub 'outside of A2:N5000
End If

For Each myCell In myIntersect.Cells
'stop _change event from firing because of the
'change the code will make
Application.EnableEvents = False
With Me.Cells(myCell.Row, "P")
'date and time???
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
'or just date
'.NumberFormat = "mm/dd/yyyy"
'.Value = Date
End With
'turn on monitoring for the next user change
Application.EnableEvents = True
Next myCell

End Sub

 

[Karen Mckenzie]

right click the sheet tab and select view code, then paste the
following

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:N5000")) Is Nothing Then
Cells(Rows.Count, "P").End(xlUp).Offset(1) = Date
End If
End Sub

the Intersect returns a range object containing the range of cells if
two or
more ranges overlap. It returns nothing if there's no overlap. So we
test
NOT is Nothing ie, there the cell changed is in the range.
If so, then we go to the bottom of the column P, end/up to the last
used
cell, offset one row down, which will be the next empty cell, and save
today's date. This will of course fire the change event again, but as P
is
outside your check area, it wont do anything.

Great, thanks. I think I need to start learning this!

 

[Karen Mckenzie]

Really helpful, thanks!!

Hi Karen

Try the below which makes use of Worksheet Change and Selection events
to
track real changes made to the worksheet. Select the sheet tab which
you want
to work with. Right click the sheet tab and click on 'View Code'. This
will
launch VBE. Paste the below code to the right blank portion. Get back
to to
workbook and try out.


Dim varData As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2:N5000")) Is Nothing
Then
If Target.Count = 1 And Target.Value <> varData Then
Application.EnableEvents = False
Range("P" & Target.Row) = Now()
Application.EnableEvents = True
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varData = Target.Value
End Sub