adding comment to cell if value changed

[tpeter]

I have found the following code online and it adds a comment to a cell if
someone changes it. It works fine in the free download and if I paste the
code into a blank workbook. But when I put it into a specific worksheet I
have the code breaks on the format date function. Any help would be great.

Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
& "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
Environ("UserName")
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$C$15:$j$15")) Is Nothing Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
End Sub

Tim Peter

 

[Mike H]

Hi,

There's nothing wring with the code, are you sure your not suffering from an
unfortunate line-wrap. The following bit of the code goes all in a single line

Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10)
& "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " &
Environ("UserName")


or you can use continuation like this and break it in 3 lines

Target.AddComment.Text Text:="Previous Value was " & _
preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & _
Chr(10) & "By " & Environ("UserName")

Mike

 

[tpeter]

Thanks for answering Mike,

That is the part I can't figure out. It works in blank workbooks but when I
put it into a current workbook I have created it breaks on format and says
"expected variable or procedure, not modual". If I take out the Format then
it works but I have no date when the cell was changed. Thanks again for your
help.

Tim Peter

 

[Don Guillett]

Have you named your module the same as the code

 

[Dave Peterson]

Just to add to Don's idea...

Check to see if you created your own function/sub named Format.

 

[tpeter]

That was it. I had a different modual nameded format. Thank all of you for
your excellent troubleshooting skills. I greatly appreciate it.

Tim Peter