When using Worksheet_Change - how can I get the value in a cell before change?

Question

In my Excel sheet, I have a range "plot" that triggers a sub-routine upon change. I used the following code for that:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("plot").Address Then
        auto_save_data (current_plot) ' <- doesn't work
        restore_data
    End If
End Sub

This code has to first save the data from the current worksheet to a specific range, that defined by current_plot in another worksheet (let's call it "DATA"), by calling auto_save_data (current_plot).
Then it restores the data from a specific range in "DATA" that is defined by Range("plot"), by calling restore_data.

The restore_data sub-routine above work as expected, but auto_save_data doesn't. The problem is that when the user change the value of "plot" I need to somehow know what was the value before the change, so I can save the data to the correct place before restoring the data from "DATA" for the value after update, and by that deleting the data in the current sheet.

I tried to use the Worksheet_SelectionChange event, as described here:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim current_plot As Variant
    If Target.Address = Range("plot").Address Then
        current_plot = Target.Value
    End If
End Sub

But it has 2 problems:

1. It didn't work. The sub-routine Worksheet_Change didn't seem to recognize the value of the variable current_plot, albeit, it didn't throw an error.
2. I tried another method from the question above, that save the old value to a hidden sheet. That worked, except when the user changes the value in "plot" without selecting another range first (then the value in the hidden sheet does not update).

So my question is: What is the simplest method (I'm very new to VBA) to use the value that was in Target before the routine Worksheet_Change was triggered?

EDIT: I changed "plot" to be a single cell range ($P$2), to focus the question on the real problem.

Answer 1

Assuming "Plot" is a single-cell range in the worksheet which has Name of "DATA", place the following code in the code module of Worksheets("DATA"):

'Declare a variable with global scope, i.e. accessible in this worksheet
'and in other code modules.
Public current_plot As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("plot").Address Then
        'Use "current_plot" whenever the cell is changed
        MsgBox "Old value: " & current_plot & vbCrLf & _
               "New value: " & Target.Value
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("plot").Address Then
        'Update "current_plot" whenever the cell is selected
        current_plot = Target.Value
    End If
End Sub

and place this code in the ThisWorkbook code module:

Private Sub Workbook_Open()
    'Initialise "current_plot" when the workbook is opened
    Worksheets("DATA").current_plot = Worksheets("DATA").Range("Plot").Value
End Sub

---

Assuming you don't want to know what used to be in the cell, but instead you actually want to know what was in the cell the last time you made use of the cell's value, you can simplify this a bit by using the following:

'Declare a variable with global scope, i.e. accessible in this worksheet
'and in other code modules.
Public current_plot As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("plot").Address Then
        'Use "current_plot" whenever the cell is changed
        MsgBox "Old value: " & current_plot & vbCrLf & _
               "New value: " & Target.Value

        '...
        '... process whatever needs to be processed
        '...

        'Save the value we used this time
        current_plot = Target.Value
    End If
End Sub

and place this code in the ThisWorkbook code module:

Private Sub Workbook_Open()
    'Initialise "current_plot" when the workbook is opened
    Worksheets("DATA").current_plot = Worksheets("DATA").Range("Plot").Value
End Sub