Printable View
I know how to get values which were updated or inserted in an excel sheet (read blank as previous value).I also want to record if a particular row was deleted and store that value also as a comment for the row that took it's place.
I know about the track changes option in excel, but I cannot use it.
I've never heard of anyone wanting to "record a replaced row before as a value". Can you explain in greater detail, what the "value" is meant to be?
The following code by Alphafrog, will store as a value within a comment, any cell value replaced within a defined range ( in this case the range is all cells in columns A & B).
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oComment As Comment, cell As Range, strPrev As String
If Not Intersect(Target, Columns("A:B")) Is Nothing Then
For Each cell In Intersect(Target, Columns("A:B"))
Set oComment = Nothing
On Error Resume Next
Set oComment = cell.Comment
On Error GoTo 0
If Not oComment Is Nothing Then
strPrev = Mid(oComment.Text, InStr(oComment.Text, "Current value: ") + 15, 999)
oComment.Text Text:="Previous value: " & strPrev & Chr(10) & _
"Changed: " & Format(Now, "mmm dd, yyyy h:mm AM/PM") & Chr(10) & _
“Current value: " & cell.Value
ElseIf Not IsEmpty(cell) Then
cell.AddComment
cell.Comment.Text Text:="Previous value: Empty" & Chr(10) & _
"Changed: " & Format(Now, "mmm dd, yyyy h:mm AM/PM") & Chr(10) & _
"Current value: " & cell.Value
cell.Comment.Shape.Width = 150
cell.Comment.Shape.Height = 35
End If
Next cell
End If
End Sub
[/vba]
I will explain what I have to do.I want to record insert update or delete events on any cell in my sheet.Now insert and update can be easily identified, but how do I know that a user deleted a whole row.Plus I need to say what was the previous value in this cell, even if I deleted the whole.Or record that a delete happened and reflect that with a comment or some fontcoloring.
Your code is very good. A couple of points, it had one syntax mistake.I rectified and secondly it still doesn't tell me what was the previous deleted value
Its not my code. As I indicated the code belongs to Alphafrog
Use the selectionchange event to capture the value before changing it.Quote:
Originally Posted by szcukg
i tried it but it helps when I delete one cell in a row.Still haven't figured out how to do for an entire row.What I need is when I delete a row say row 3, then row3 should have a red color border and a comment on it which tells me the deleted value.Quote:
Originally Posted by xld
If its a "value" as you continue to refer to, then you are basicily deleting a cell. If its a row your deleting, then it's more likely to be a combination of "values". As I asked you in post#2 "What is the value meant to be?" I've yet to see your description of this "value".
Ok my bad.I shoulld have kept it values.Yes I want to delete an entire row and that makes it a set of values.Quote:
Originally Posted by Aussiebear