VBA insert comment only when cell in a range changes first time

[Heiko Kausch]

Hi,
Looking for an Excel VBA code that will look for changes in a specific range and record original value and display as a comment, including date and author. If original value is blank (empty), then ignore code. We are not interested in recording subsequent chnages, but it must record the very first change. Therefore, if the cell already has a comment, it will assume the initial change has been recorded.
Can anyone help?
Heiko

 

[Claus Busch]

Hi Heiko,

Am Tue, 21 Jan 2014 05:06:23 -0800 (PST) schrieb Heiko Kausch:

Looking for an Excel VBA code that will look for changes in a specific range and record original value and display as a comment, including date and author. If original value is blank (empty), then ignore code. We are not interested in recording subsequent chnages, but it must record the very first change. Therefore, if the cell already has a comment, it will assume the initial change has been recorded.

put following code into the code module of the sheet (changes will be
recorded in column A):

Option Explicit
Dim WertAlt

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 1 Or Target.Count > 1 Then Exit Sub

If WertAlt <> "" And Target <> WertAlt And Target.Comment Is Nothing
Then
Target.AddComment "Alter Wert: " & WertAlt & Chr(10) _
& "geändert am: " & Date & Chr(10) _
& "von: " & Application.UserName
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 1 Or Target.Count > 1 Then Exit Sub

WertAlt = IIf(Len(Target) > 0, Target, "")

End Sub


Regards
Claus B.

 

[Heiko Kausch]

That's worked a treat, thanks!
How can I apply this to column "P" (Column 16) only?
Or say, I want to choose a set of different columns but not others?
Heiko

 

[Claus Busch]

Hi,

Am Tue, 21 Jan 2014 06:23:06 -0800 (PST) schrieb Heiko Kausch:

How can I apply this to column "P" (Column 16) only?
Or say, I want to choose a set of different columns but not others?

for column 16:
If Target.Column <> 16then exit sub

Or for column 4 to 8:
If Target.Column < 4 or target.column > 8 then exit sub



Regards
Claus B.

 

[Claus Busch]

Hi Heiko,

Am Tue, 21 Jan 2014 15:27:21 +0100 schrieb Claus Busch:

for column 16:
If Target.Column <> 16then exit sub

Or for column 4 to 8:
If Target.Column < 4 or target.column > 8 then exit sub

or for a specific range e.g. D1:G100 try:

If Intersect(Target, Range("D1:G100")) Is Nothing _
Or Target.Count > 1 Then Exit Sub


Regards
Claus B.

 

[Heiko Kausch]

Dankeschön, Claus.