Everytime a cell changes in a named range, I want the code to clear existing comments, then record the old value as a new comment. I have to use Worksheet_Calculate because the cell changes based on a formula. I have tried several, methods, but none are working...
Code
In this case, it adds a comment to every cell.
Code
- Option Explicit
- Public preValue As Variant
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Count > 1 Then Exit Sub
- Target.ClearComments
- Target.AddComment.Text Text:="Auparavant " & preValue & Chr(10) & "Actuellement " _
- & Format(Date, "mm-dd-yyyy") & Chr(10) & "Modification par " & Environ("UserName")
- End Sub
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- If Target.Count > 1 Then Exit Sub
- If Target = "" Then
- preValue = "a blank"
- Else: preValue = Target.Value
- End If
- End Sub
This works only when I change the cell manually.