Preserving Event Target Value

[Hammer_757]

This is a greatly simplified report of a previous question. I have simplified the file to just the problem eliminating a lot of clutter and hopefully it is more palatable.

I need to compare the before and after change value of cells in a sheet. I am trying to use the Target value of the Worksheet_Change and Worksheet_SelectionChange events in this endeavor.
The idea is to note the Target value of Worksheet_SelectionChange when the cell is selected, note the Target value of Worksheet_Change when the value of the cell is changed and compare the two. If the two are different call the code.

The problem is that even though the Worksheet_SelectionChange Target is “imported” to the Worksheet_Change via the variable “TargetWksSelChng”, the variable changes as soon as the cell value has been changed, before the comparison has been made.

The results from the immediate window are:
From Worksheet_SelectionChange--------------------
TargetWksSelChng = 1
From Worksheet_Change-----------------------------
TargetWksChng = 3
TargetWksSelChng = 3
From Worksheet_SelectionChange--------------------
TargetWksSelChng =

Starting by selecting a cell with a value of 1
Changing the cell value to 3
And after [ Enter] focus is on an empty cell.

Is there some way I can trap the Worksheet_SelectionChange Target and preserve that value until I have completed the comparison?
I thought Application.EnableEvents = False would save me but not so…

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Hammer_757]

PreserveTarget.xlsm

File attached

[StephenR]

I think the issue is that you are assigning ranges to the variables rather than the values so the values are being updated. Try this:

Please Login or Register  to view this content.

[Hammer_757]

Thanks Stephen, that works great. I knew it was probably something simple. I also defined the two variables as strings.