Detect Cell Changes

[aga2957]

I have an excel sheet that I designed to look like a form. That form has
default values. Now, what I want to do is that, whenever there is a
change in any cell, let's say for example, someone change the value of
A4, that change will be recorded in C5 for example.

Let's have a working example,

A4 default value = 5

When someone change the value of A4, C5 will show A4. Meaning, A4 has
been changed.

So goes with other cells with default values.

Let's say for example, the value of A5 has been changed, then C5 will
now show "A4, A5" meaning, these cells were changed.

Thank you so much for your assistance!

 

[Tim Williams]

What if the user changes the value but then changes it back to the orginal
(default) value ?
Would you still want to log that ?

Tim

 

[Trevor Shuttleworth]

Starter for 10:

In the Worksheet Class Module (the code behind the worksheet)

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Range("C5") = "" Then
Range("C5").Value = Target.Address(False, False)
Else
Range("C5").Value = Range("C5").Value _
& ", " & Target.Address(False, False)
End If
Application.EnableEvents = True
End Sub

Regards

Trevor

 

[aga2957]

If possible, it shouldn't be log since it was changed to its original
state or value. But if not, it's would be fine.

Thank you so much!

 

[NickHK]

As long as you are not intending to use these sheets in HTML, you can use
the ID property of the cell like the .Tag property of some controls.
You could set the default value = to this then compare the .Value to the .ID
to see if you need to log the change or not.

With Target
If .Value <> .ID Then
'Log it
Else
'clear log for that cell
End If
End With

However, these ID values are not stored with the workbook, so you would have
fill them will when you (re-)open the WB each time.
If this is from a template, you can loop through the cells in question,
setting .ID=.Value.

Otherwise it may be of limited use and it may be better to store the default
values elsewhere, possibly on a hidden sheet.

NickHK