keep track of previous excel range value after dragdropped...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am developing a simple app and this app requires keeping track of previous
value entered into a cell before dragging new value on it.

I have an external source from which I dragdrop values into excel cell; but
some cells on my excel sheet already contains data - so I want to append this
new dragged data to my previously existing data on that cell. Which event on
excel should I use to keep track of it?

Currently I am using Workbook_SheetChange event to do this; but when I
dragdrop data on a particular cell that already contains data;
range.cells.value2 in workbook_sheetchange or workbook_SheetSelectionChange
events are giving me the dragged data and overwriting previous existing data
on that cell. How to resolve this issue?

Any help is really appreciated!!

Thank You!!
 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oldval as Variant
Dim newval as Variant
On Error goto ErrHandler
if lcase(sh.name) = "myname" then
if not intersect(target,worksheets("myname" _
).Range("B5:F30")) is Nothing then
newVal = Target.Value
Application.EnableEvents = False
Application.Undo
Oldval = Target.Value
Target.value = newval

' your decision logic here

end if
End if

ErrHandler:
Application.EnableEvents = True
End Sub
 
Thanks Tom!! It worked.

Tom Ogilvy said:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim oldval as Variant
Dim newval as Variant
On Error goto ErrHandler
if lcase(sh.name) = "myname" then
if not intersect(target,worksheets("myname" _
).Range("B5:F30")) is Nothing then
newVal = Target.Value
Application.EnableEvents = False
Application.Undo
Oldval = Target.Value
Target.value = newval

' your decision logic here

end if
End if

ErrHandler:
Application.EnableEvents = True
End Sub
Click to expand...
 
Back
Top