Getting a Previous Cell Value

[Peter]

Hi,
I have trawled through this group trying to find a solution to what I
would think is a simple problem:

I have a VB add-in that supports drag and drop. The problem is that I
need to know the previous value of a cell unto which another cell has
been dropped.

Once the SheetSelectionChange event is triggered the cell has already
been changed, making it impossible to establish the previous value.
Is this really that difficult?????

Help!

Thanks,
Peter.

 

[Sharad Naik]

Yes it IS difficult.
Instead of Selection Change it can be done in Worksheet_Change, with certain
conditions.
Below code will work if only a single cell is drag-dropped.
When a cell is drag-dropped, it firews two consecutive Worksheet_Change
events,
each for the source and destination cell.
And these two events occur in fraction of seconds .. well Unless the code
is not delaying it.
So I used timer to exit sub if the next event is occurrs within the second.
The variable preVal below will hold the previous value as required by you.
What you should do is, put this code in worksheet_change procedure,
just to assign the previous value to variable preVal. Define preVal at
module level
as public.
Write your code else where and use preVal there.
To check the below code is working, you may like to add 'MsgBox preVal'.
But do not add this in the worksheet_change code below. Because you can not
respond to the msgbox within a second, so you will get two message boxes,
first one will show correct value, second one the wrong value (current value
instead
of previous)
Write a seperate macro and add msgbox preVal there.
Below code should go the Sheet's Change event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
Static notAgain As Boolean, strAdd As String, myTime As Long
If Target.Cells.Count > 1 Then Exit Sub
If notAgain Then Exit Sub
If myTime = Timer Then Exit Sub
notAgain = True
myTime = Timer
strAdd = Target.Address
Application.Undo
preVal = Range(strAdd).Value
Application.Undo
notAgain = False
myTime = 0
End Sub

Then insert a module and Define Public Variable preVal as under , in General
Section.
Option Explicit
Public preVal

And add a macro to check the value of preVal as under:
Sub CheckVal()
MsgBox preVal
End Sub

Drag-Drop a Cell then run above CheckVal() macro to check the value.
Or even type something in a cell (edit and change a cell value) and run
above CheckVal()
macro.

Sharad