Comments box

J

Jock W

Is it possible to have Excel to automatically add a 'comment' with the date
already in the comment to a cell when data is put into that cell?

tia
 
C

CLR

This crude Workbook code seems to do it........
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If ActiveCell.Value <> "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
End Sub
 
C

CLR

If the feature becomes aggravating, this version will allow you to toggle it
On and Off by typing "CommentOn" in cell A2.....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Upon entering a value in a cell, this macro will attach a Comment Box
'to the cell and insert the current date. It will append existing comments.
If Range("A2").Value = "CommentOn" Then
If ActiveCell.Value <> "" Then
ActiveCell.Select
If Selection.Comment Is Nothing Then
Selection.AddComment
Selection.Comment.Visible = True
Selection.Comment.Text Text:=Date & Chr(10)
Selection.Comment.Visible = False
Else
Selection.Comment.Visible = True
Selection.Comment.Text Text:=ActiveCell.Comment.Text & Chr(10) & Date &
Chr(10)
Selection.Comment.Visible = False
End If
End If
Else
End If

End Sub

Vaya con Dios,
Chuck, CABGx3
 
J

Jock W

Tried and tried but I can't get this to work.
No comment forthcoming.
Is this bit correct "ByVal Sh As Object, ByVal Target As Range"?

Cheers
 
C

CLR

Did you watch out for any word-wrap coming through the post....sometimes that
messes macros up. I copied the code straight out of my VBE....it was working
there. Check each line for something funny looking. Worse case is, give me
your email and I'll send a file with it working in it.

hth
Vaya con Dios,
Chuck, CABGx3
 
J

Jock W

It all looks ok to me, but it still no joy.
I have tried adding the code to the whole work book and also the worksheet,
but neither give a resukt.
email addy - [email protected]

tx
 
Top