Track the Changes

[santhosha]

Hi Everyone,


Here is the problem.


Let us just assume that the cell A1 contains word "santhosh"


Now my question is when we make some changes to cell A1, the cell B1 should return the time of such changes.(say it as 12/23/2011 11.57 am)

Can we develop a formula in cell B1 to track such changes.

 

[Luke M]

Can't do it using a formula, but you could use a worksheet change event. To use this one, right click on sheet tab, view code, paste this in.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Range("B1") = Now
Application.EnableEvents = True
End Sub

[/pre]

 

[rsk]

Hi,


In shared workbook Column A contains Fruit's name

For eg:

A1 = Apple ; A2 = Orange ; A3 = Grapes etc

if some one changed the item from A1 "Apple" to "Pineapple" then

I want notification that A1 has been changed from "Apple" to "Pineapple" by username


Notification can be in any way like msgbox or any particular cell

 

[SirJB7]

Hi, rsk!

You can modify Luke M's post to something like this:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column<>1 or Target.Cells.Count>1 Then Exit Sub

Application.EnableEvents = False

Target.Offset(0,1).Value = Now

Target.Offset(0,2).Value = Application.Username

Application.EnableEvents = True

End Sub


If you want a kind of journal of each change (like a log or history) you should modify the 1 and the 2 Offset lines to the first empty column in Target.Row for 1, and plus 1 for 2.

In this way you'll have all the subsequent changes identified by time and username (not userid, if somebody knows how to get the userid, please let me know).

I think that msgbox won't be effective because, a) this is a shared workbook and you want to see the changes on line (in this case I don't know how to display a message box to you and not to the users that change column A cells), b) you open later the last version saved of the workbook (in this case if there were a lot of changes, the message box size may be suitable for displaying all the changes).

So, a history in the same row (last change or sequence of changes) will be the best approach. And if you can use columns B and adjacents, you can build the journaling on other righter columns, on another sheet or book: just update references in Target.Offset lines.


Hope it works. Thanks to Luke M for the appropriation of the structure code.

Regards!


PS: you might also want to track the previous value, but I think you can't do that from within the Change event code, maybe you can mix this event and the SelectionChange event to perform that action (I still don't know and think it isn't possible to obtain the "previous" value of a changed cell... again, if somebody knows... here I am)

 

[Luke M]

SirJB7,

Here's an idea of how I would go about finding the previous value.

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
'Only do this if working with 1 cell
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False

Dim NewValue As Variant
Dim OldValue As Variant

NewValue = Range("A1").Value
Application.Undo
OldValue = Range("A1").Value

'You now have both values stored
'Do whatever code you need

Range("A1").Value = NewValue
Application.EnableEvents = True

End Sub

[/pre]

 

[Clarity]

Hi,


Another option is to turn on track changes, see link below:

http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx


(nb sharing a workbook does restrict some functionality so it may not be suitable for all situations).


There are also software solutions designed for tracking changes if you need something more robust.

 

[Clarity]

Hi,


Another option would be to use Excel's built in Trach Changes function, see link below:

http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx


(NB Tracking changes makes the workbook shared. This does have someimplications for some functionality so may not be suitable for all situations)


There are also software solutions designed specifically to track changes in spreadsheets if you need something robust and auditable.

 

[Clarity]

Hi,


Another option would be to use Excel's built in Trach Changes function, see link below:

http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx


(NB Tracking changes makes the workbook shared. This does have someimplications for some functionality so may not be suitable for all situations)


There are also software solutions designed specifically to track changes in spreadsheets if you need something robust and auditable.

 

[Clarity]

Hi,


Another option would be to use Excel's built in Trach Changes function, see link below:

http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx


(NB Tracking changes makes the workbook shared. This does have someimplications for some functionality so may not be suitable for all situations)


There are also software solutions designed specifically to track changes in spreadsheets if you need something robust and auditable.

 

[Clarity]

Hi,


Another option would be to use Excel's built in Track Changes function.


http://office.microsoft.com/en-us/excel-help/track-changes-in-a-shared-workbook-HP010197381.aspx


(NB Tracking changes makes the workbook shared. This does have some implications for some functionality so may not be suitable for all situations)


There are also software solutions designed specifically to track changes in spreadsheets if you need something robust and auditable.

 

[SirJB7]

@Luke M

Hi!

Nice approach, I didn't think about the Undo method.

Thank you very much, Luke M.

Regards!