Cell Change to Comment
 

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 A1:P500

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

Cell Change to Comment
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:P500")) 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


" wrote:

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 A1:P500

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

Cell Change to Comment
 

On May 30, 5:38*pm, Gary''s Student
wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:P500")) 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



" wrote:
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 A1:P500

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- Hide quoted text -

- Show quoted text -

Thanks Gary!!

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