Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
r = Selection.Row
c = Selection.Column
'Target Cell
Set rng = Sheet1.Cells(r, c)
'Checks if cell is blank and has no comment
If rng.Value = "" And rng.Comment Is Nothing Then
Sheet1.Unprotect Password:="pass"
rng.AddComment "Cell value was deleted on " & Now & "."
'Checks if cell is blank but has comment
ElseIf rng.Value = "" Then
Sheet1.Unprotect Password:="pass"
rng.Comment.Text "Cell value was deleted on " & Now & "." & vbLf, , False
Else
'Adds comment if cell isn't blank, but contains a comment
If rng.Value <> "" And rng.Comment Is Nothing Then
Sheet1.Unprotect Password:="pass"
rng.AddComment "Cell value was edited on " & Now & "."
Else
'Appends comment
Sheet1.Unprotect Password:="pass"
rng.Comment.Text "Cell value was edited on " & Now & ". " & vbLf, , False
rng.Comment.Shape.TextFrame.AutoSize = True
'Auto-sizing comment
If rng.Comment.Shape.Width > 300 Then
lArea = rng.Comment.Shape.Width * rng.Comment.Shape.Height
rng.Comment.Shape.Width = 200
' An adjustment factor of 1.1
' seems to work ok.
rng.Comment.Shape.Height = (lArea / 200) * 1.1
End If
End If
End If
'Protects the sheet
protector
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
r = Selection.Row
c = Selection.Column
'Unlocks the cell if only a single cell is selected
If Selection.Rows.Count = 1 And Selection.Columns.Count = 1 Then
Sheet1.Cells(r, c).Locked = False
End If
End Sub