Worksheet Change Event - Print the cell value before and after.

[Ebraham]

hi all,

I have my below code which gives me the value after a particular cell value is change. I want to see what was the cell value before and what the value is after it is changed.

[TABLE="width: 64"]
 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
  [TD="width: 64"]Private Sub  Worksheet_SelectionChange(ByVal Target As Range)[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Dim fso As Scripting.FileSystemObject[/TD]
 [/TR]
 [TR]
  [TD]Dim changelog As Scripting.TextStream[/TD]
 [/TR]
 [TR]
  [TD]Dim r As Range[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Set fso = New Scripting.FileSystemObject[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Path = Environ("UserProfile")[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Set changelog =  fso.OpenTextFile(Filename:=Environ("UserProfile") &  "\Desktop\Practice\Temp\Changes.txt", IOMode:=ForAppending,  Create:=True)[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]If Target.Cells.CountLarge > 1000 Then[/TD]
 [/TR]
 [TR]
  [TD]     changelog.Write Now & vbTab[/TD]
 [/TR]
 [TR]
  [TD]     changelog.Write Environ("Username") & vbTab[/TD]
 [/TR]
 [TR]
  [TD]     changelog.Write Target.Address & vbTab[/TD]
 [/TR]
 [TR]
  [TD]     changelog.Write "Multiple values changed" & vbNewLine[/TD]
 [/TR]
 [TR]
  [TD]Else[/TD]
 [/TR]
 [TR]
  [TD]     For Each r In Target[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]     changelog.Write Now & vbTab[/TD]
 [/TR]
 [TR]
  [TD]     changelog.Write Environ("UserName") & vbTab[/TD]
 [/TR]
 [TR]
  [TD]     changelog.Write r.Value & vbNewLine[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]     Next r[/TD]
 [/TR]
 [TR]
  [TD]End If[/TD]
 [/TR]
 [TR]
  [TD]    [/TD]
 [/TR]
 [TR]
  [TD]changelog.Close[/TD]
 [/TR]
 [TR]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ebraham]

Hi All,

I am trying to record the changes in the worksheet. Cells values changed from and cell value change to. I am not able to get value changed to properly. Kindly help advise ? is that possible.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim fso As Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim r As Range


Set ts = fso.OpenTextFile(Environ("UserProfile") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "ChangeLog.txt", ForAppending, True)


For Each r In Target
    ts.Write Now() & vbTab
    ts.Write Environ("Username") & vbTab
    ts.Write Target.Address & vbTab
    ts.Write "Value change From : " & r.Value & vbTab & "Value change to : " & Range(Target.Address).Value & vbNewLine
Next r




End Sub

 

[John_w]

There are several solutions at https://stackoverflow.com/questions...-the-old-value-of-a-changed-cell-in-excel-vba

The Dictionary or hidden sheet method could be combined with your ChangeLog.txt file.