Log previous value in seperate worksheet cell

Hi,

I'm looking for a "history" function for cells that are set by a formula i.e.:

Cell A1 has value 0
Cell A1 is set to value 6 -> Cell B1 is set to 0
Cell A1 is set to value 10 -> Cell B1 is set to 6
Cell A1 is set to value 16  -> Cell B1 is set to 10
etc

Any ideas?

Br Jonas
decisionfocusAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
I'm not sure what update scenario does..In that case you can move this code like a normal module declare your range and simply move it their but not sure it will fully work and do what you are looking for...
0
 
ProfessorJimJamCommented:
I have developed something like this, I am driving right now. As soon as I get home I will post the code
0
 
Roy CoxGroup Finance ManagerCommented:
You could store the previous value in a cell comment. I can provide code if you want
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
decisionfocusAuthor Commented:
Sounds great, thank you!
0
 
decisionfocusAuthor Commented:
@Roy_Cox: would the values stored in comments be accessible to my code? I need the delta btw the current value and the previous value in some other code.
0
 
ProfessorJimJamCommented:
put this code into the worksheet module.  not in a simple module.

the below code will work for any change in any cell in entire worksheet

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Target.ClearComments
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then
        preValue = "a blank"
    Else: preValue = Target.Value
    End If
End Sub

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
Here's an example
Example File
0
 
ProfessorJimJamCommented:
the below code is for specific range

change the range as per your need $C$4:$H$15 to your specified range

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$C$4:$H$15")) Is Nothing Then Exit Sub
    Target.ClearComments
    Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$C$4:$H$15")) Is Nothing Then Exit Sub
    If Target = "" Then
        preValue = "a blank"
    Else: preValue = Target.Value
    End If
End Sub

Open in new window

0
 
ProfessorJimJamCommented:
if you do not know how to put this code and where.  see attached file
EE.xlsm
0
 
ProfessorJimJamCommented:
and this one is my favorite that keeps all the records. see attached
EE.xlsm
0
 
ProfessorJimJamCommented:
Roy,
i checked your file. it does not add the track changes of cell values if it is other than text.   it only works with text but if i change a cell value to number then it does not work.
0
 
Roy CoxGroup Finance ManagerCommented:
I have to go out for an hour and I'll check when I get back
0
 
Roy CoxGroup Finance ManagerCommented:
Easier than I thought. Use this code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next

    Dim Proceed As Long
    Proceed = MsgBox("Do you want to add the text of your cells to each comment?    " & vbCrLf & _
                     "This will delete any existing comment text", vbQuestion + vbYesNo)
    If Not Proceed = vbYes Then Exit Sub
    With Target
        If .Comment Is Nothing Then
            .AddComment
            .Comment.Text CStr(.Value)
        Else: .Comment.Text Text:=CStr(.Value)
        End If
    End With
    On Error GoTo 0
End Sub

Open in new window


If you want code to write the saved value back to the cell let me know
0
 
decisionfocusAuthor Commented:
Hi guys,

Thanks - Almost what i was looking for!

However, the cell(s) i'm trying to log historical values for contains a formula. I've attached a sheet that hopefully explains better what i'm trying to accomplish.

Thanks & regards
EE-Example.xlsx
0
 
Saurabh Singh TeotiaCommented:
Decisionfocus,

What you are looking forward for it's a bit tricky to do since in excel you don't have any before-calculation event..So now in order to achieve this i'm setting up your calculations to manual when you open your workbook so that your values doesn't get updated now once you enter a new value in the range you defined in your example i pick up values from your range and move it over to your desired range force the calculations and again turn it back to manual that way it will do what you are looking for..

In additional when you close this excel file your calculations are back to automatic again..

Refer to enclosed file for working example as it does what you are looking for...

Saurabh...
EE-Example.xlsm
0
 
decisionfocusAuthor Commented:
Saurabh, that's extremely useful. It does exactly what i want!

It seems, however, that i hadn't properly thought my needs through. I need this to only execute when another sub in the same sheet is executed, i.e. to call it from that sub:

Public Sub UpdateScenario(ByVal Target As Range, Cancel As Boolean)

      call Worksheet_Change....


However, the target here is different so i'm a bit unsure of how to do this. Any ideas would be very appreciated!

Br Jonas
0
 
Roy CoxGroup Finance ManagerCommented:
There is no code in your example workbook. Please attach an example with the code in.
0
 
decisionfocusAuthor Commented:
I adapted Saurabhs code and made it work. Thanks for your help!

Br Jonas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.