Determining cell value prior to change

[dunk123]

Hi all

I have the below code that displays the changes made to cell values prior to saving the workbook. What I'm trying to figure out how to do, is place the value of the cell prior to the change into a variable and display the old value as well as the new value when the file is saved. The below obviously doesn't work as it simply uses the activecell, not the one that was changed... Thanks for the help.

Private sCellsChanged As String
Private sCellOriginalValue As String


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
   If sCellsChanged <> "" Then
      MsgBox sCellsChanged
      sCellsChanged = ""
      sCellOriginalValue = ""
   End If
   
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   
   sCellOriginalValue = ActiveCell.Value
   sCellsChanged = sCellsChanged & Sh.Name & " " & Target.Address & " Old Value " & sCellOriginalValue & " New Value: " & Target.Value & vbCrLf

End Sub

 

[dunk123]

I'm still struggling to figure this out. Thought about trying to use SheetSelectionChange event. Is that something worth pursuing?

 

[Ruddles]

As a general technique, when a cell changes and you want to store its old value, you can do this:-

[FONT=Courier New]newvalue=target.value           [COLOR=green] ' save the cell's new (changed) value[/COLOR][/FONT]
[FONT=Courier New]application.undo                [COLOR=green] ' undo the change to the cell[/COLOR][/FONT]
[FONT=Courier New]oldvalue=target.value           [COLOR=green] ' save the cell's old value[/COLOR][/FONT]
[FONT=Courier New]application.enableevents=false   [COLOR=green]' disable change events![/COLOR][/FONT]
[FONT=Courier New]target=newvalue                 [COLOR=green] ' put the new value back in the cell[/COLOR][/FONT]
[FONT=Courier New]application.enableevents=true    [COLOR=green]' re-enable change events[/COLOR][/FONT]

Is that any help?

 

[VoG]

If you are trying to create a log of changes take a look here (sorry, long thread ) http://www.mrexcel.com/forum/showthread.php?t=381604

 

[dunk123]

That could work. Let me play around with that and I'll post back the final code (or next question!)

Much appreciated.

 

[dunk123]

I'll check out the log of changes thread as that's an interesting concept. Thanks for the link.

As for the code from Ruddles, I had one issue with the undo part crashing excel entirely. I ended up moving the enable events to the beginning and this went away. If anyone can shed any light on why it was happening and if moving it is a suitable fix, I'd be interested to know.

Other changes in the below:
I added a capture of the cell address the user exits to (with the return or tab button), and at the end the reselection of that cell, so the normal behavior of exiting a cell was maintained. Prior to this change the active cell selected after changing a cell value would be the one that had been changed.

I altered the information being shown when saving to show the old value and new value.

And finally, I added the clearing of all variables after the msgbox is click away.

Thanks again for the help. Anyone else wishing to use this, this code goes in the ThisWorkbook object.

Private sCellsChanged As String
Private sOldValue As String
Private sNewValue As String
Private sCellAddress As String

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
   Application.EnableEvents = True
   
   If sCellsChanged <> "" Then
      MsgBox sCellsChanged
      sCellsChanged = ""
      sOldValue = ""
      sNewValue = ""
      sCellAddress = ""
   End If
   
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Application.EnableEvents = False    ' disable change events!
    sNewValue = Target.Value            ' save the cell's new (changed) value
    sCellAddress = ActiveCell.Address   ' save the cell address the user exited to
    Application.Undo                    ' undo the change to the cell
    sOldValue = Target.Value            ' save the cell's old value
    Target = sNewValue                  ' put the new value back in the cell
    Range(sCellAddress).Select          ' make active cell the one the user exited to
    Application.EnableEvents = True     ' re-enable change events
    
    sCellsChanged = sCellsChanged & Sh.Name & " " & Target.Address & " Old Value: " & sOldValue & " New Value: " & sNewValue & vbCrLf

End Sub

 

[Ruddles]

I had one issue with the undo part crashing excel entirely. I ended up moving the enable events to the beginning and this went away. If anyone can shed any light on why it was happening and if moving it is a suitable fix, I'd be interested to know.

Sorry, you're right, and you moved the EnableEvents = False to the correct place: you have to disable events before you do the Undo otherwise the Undo triggers the event handler recursively until the stack fills up and Excel crashes.

Apologies again, and I'm relieved my error doesn't appear to have caused you too much trouble.