Determining a previous cell value if deleted
 

Hi, this may seem strange but I want to be able to determine within the
Worksheet_change code what the value of a cell was if someone hit the delete
key to erase it. What I have done is setup different "modes" via macros that
set a defined name to a yes or no. If not in a certain mode, I don't want the
user to erase some fields. I know I could keep a hidden column with a shadow
copy that I pickup and restore the other from. In fact, I do have some of
that already but there is so much interaction be sheets and code, adding
additional columns almost drives one to drink as there is always somewhere
you forget.

Another option would be to set some sort of protect on fields from the
enabling/disabling macro that if someone does try a delete, my code would get
control and be able to inform the user of the error of their ways. I am
trying not to use the Excel locked cell scheme as most of the time the
protection will be off. This is just to try to keep users from shooting
themselves in the foot.
--
Randy

Determining a previous cell value if deleted
 

You could possibly do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
Dim i As Long
Dim v As Variant
Dim cell As Range
If Not Intersect(Target, _
Range("A1,B3:B10,C15,D2:D6")) Is Nothing Then
Application.EnableEvents = False
i = 0
ReDim v(0 To Target.Count - 1)
For Each cell In Target
v(i) = cell.Value
i = i + 1
Next
Application.Undo
' perform your checks and corrections

' restore values as appropriate
i = 0
For Each cell In Target
cell.Value = v(i)
i = i + 1
Next
End If
ErrHandler:
Application.EnableEvents = True
End Sub


Make value adjustments in the v array before restoring

--
Regards,
Tom Ogilvy

"Randy" wrote in message
...
Hi, this may seem strange but I want to be able to determine within the
Worksheet_change code what the value of a cell was if someone hit the
delete
key to erase it. What I have done is setup different "modes" via macros
that
set a defined name to a yes or no. If not in a certain mode, I don't want
the
user to erase some fields. I know I could keep a hidden column with a
shadow
copy that I pickup and restore the other from. In fact, I do have some of
that already but there is so much interaction be sheets and code, adding
additional columns almost drives one to drink as there is always somewhere
you forget.

Another option would be to set some sort of protect on fields from the
enabling/disabling macro that if someone does try a delete, my code would
get
control and be able to inform the user of the error of their ways. I am
trying not to use the Excel locked cell scheme as most of the time the
protection will be off. This is just to try to keep users from shooting
themselves in the foot.
--
Randy