Cell Change to Comment

[buck.chicagocraigslist]

Hi Everyone,

I found this code which is perfect for what I need to accomplish
except it doesn't do Formulas. i.e. Vlookups or direct links to other
workbooks.

Also this will only do Cell E5 in the work sheet I put it in. I would
like to get it to do it for cells A1500

I am a noob when it comes to VBA so please hang with me if I ask a
stupid question or two... ok three ;-)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
If Target.Row <> 5 Then Exit Sub
Dim ccc As String
ccc = Format(Date + Time, "mm/dd/yy hh:mm") _
& " " & Target.Value ' -- Application.UserName
If Target.Comment Is Nothing Then
Target.AddComment.Text ccc
Else
Target.Comment.Text (Target.Comment.Text & Chr(10) & ccc)
End If
Target.Comment.Shape.TextFrame.AutoSize = True
End Sub

Again any help would be appreciate. Thanks in Advance!!

Buck

 

[Gary''s Student]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1500")) Is Nothing Then Exit Sub
Dim ccc As String
ccc = Format(Date + Time, "mm/dd/yy hh:mm") _
& " " & Target.Value ' -- Application.UserName
If Target.Comment Is Nothing Then
Target.AddComment.Text ccc
Else
Target.Comment.Text (Target.Comment.Text & Chr(10) & ccc)
End If
Target.Comment.Shape.TextFrame.AutoSize = True
End Sub


This version will cover A1 thru P500.

 

[buck.chicagocraigslist]

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1500")) Is Nothing Then Exit Sub
Dim ccc As String
ccc = Format(Date + Time, "mm/dd/yy hh:mm") _
      & " " & Target.Value  ' -- Application.UserName
If Target.Comment Is Nothing Then
      Target.AddComment.Text ccc
Else
      Target.Comment.Text (Target.Comment.Text & Chr(10) & ccc)
End If
Target.Comment.Shape.TextFrame.AutoSize = True
End Sub

This version will cover A1 thru P500.
--
Gary''s Student - gsnu2007i











- Show quoted text -

Thanks Gary!!

Anyone have an idea on allowing this script to see Formulas?