Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
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 -- Jacob "karen" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
karen;582804 Wrote: 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 ------------------------------------------------------------------------ Karen Mckenzie's Profile: 1287 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161285 Microsoft Office Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
marcus;582815 Wrote: 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!! -- Karen Mckenzie ------------------------------------------------------------------------ Karen Mckenzie's Profile: 1287 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161285 Microsoft Office Help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
One mo
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 wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
Patrick Molloy;582824 Wrote: 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" wrote in message ... 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 Great, thanks. I think I need to start learning this! -- Karen Mckenzie ------------------------------------------------------------------------ Karen Mckenzie's Profile: 1287 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161285 Microsoft Office Help |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet Change Event - track changes
Really helpful, thanks!! Jacob Skaria;582812 Wrote: 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 -- Jacob "karen" wrote: 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 -- Karen Mckenzie ------------------------------------------------------------------------ Karen Mckenzie's Profile: 1287 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=161285 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to export/save the track change history into another worksheet? | Excel Programming | |||
Change Event Track | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
Using VBA to track changes in a worksheet - help! Event procedure?? | Excel Programming |