Finding old value of cell in Excel

[Waud]

I am trying to write a function in Excel(97) that when a user enters a value into a cell it compares the new value that has been entered and the old value of the cell, and returns the difference between these 2 values.

I realise that I could do this by using to different cells but unfortunately this is not feasible.

I struggling with this though as I can't seem to find a way of getting the old value of the cell. Anyone got any ideas as to how I'd do this. The only thing I can think of at the moment is storing the new value, undoing the change and storing the old value then redoing the change.

Any help gratefully appreciated

Waud

 

[SkipVought]

Waud,

When the user selects this cell, use the Worksheet_SelectionChange event to write the contents to a hidden sheet.

When the user changes the value of this cell, use the Worksheet_Change event to compare the value with the value in the hidden sheet.

Assuming that A1 is the cell...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Target
    If .Count > 1 then exit sub
    If .Address = "A1" Then
      MyHiddenSheet.[A1] = .Value
    End If
  End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Count > 1 then exit sub
    If .Address = "A1" Then
      MsgBox MyHiddenSheet.[A1] - .Value
    End If
  End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Skip - that's the way I used to do it but if you use a public variable for the "Old Value", you don't need to write to a hidden sheet - just store the value in the public variable and you can then reference it for the sheets CHANGE event

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

Will the Public Variable be persistent thru the entire session? I seem to recall having tried that once some time back and having the Public Variable "forget".

But mebe I had a brain fart or sumthin???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

I think it is...not tested it too much but something like this should be workable:

Public oVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Value & " old Val = " & oVal
End Sub


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

Wouldn't be surprised if there was stuff you could do that did make the public variable "forget" - just to keep us on our toes !!

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Waud]

Cheers for the help guys. I've now got the fuction working

 

[SkipVought]

Of course! It was that good ol' 386.

Or mebe it was my trusty TRS 80.

Or wuz it the Sinclair ZX-80???

I ferget!!!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884