Solved: comment used to track changes

[davidw]

I am using the comment for cells to track changes on a database. It currently list the date of the change and what it was changed from. I want it to show the label as to whom the item belonged to. I have attached a sample of what i have concerning this issue.

[mikerickson]

When I tested it, the code in that workbook created a comment in C9 that reads

PREVIOUS VALUES BELOW
190258 3/27/2009
123 3/27/2009

What text do you want the comment to have?

[davidw]

after the date i want it to show who the item had belonged to... located in column B... when done it should show what the number was, then the date, then who it belonged to .

[mdmackillop]

This will store the changes in Column 3 in a sheet called "Record" and show the recorded notes in the comment box

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
With Sheets("Record").Range(Target.Address)
.Value = .Value & vbCr & Environ("username") & ":" & date & ":" & Target.Value & vbCr
If Target.Comment Is Nothing Then Target.AddComment
Target.Comment.Text Sheets("Record").Range(Target.Address).Text
End With
End Sub

[mdmackillop]

Just seen your last post. Try this instead

Dim Temp As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
Temp = Target
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
With Sheets("Record").Range(Target.Address)
.Value = .Value & vbCr & Target.Offset(, -1) & ":" & date & ":" & Temp & " " & vbCr
If Target.Comment Is Nothing Then Target.AddComment
Target.Comment.Text Sheets("Record").Range(Target.Address).Text
End With
End Sub

[davidw]

the only thing i want is to add the data in column B. The numbers on the equipment are listed from column C to CZ.

[davidw]

I want it just the way it is except i want to add the person in column B

[mikerickson]

Changing this line in Sheet1's Change event should do it.
.Comment.Text Text:=COMMENT_HEAD & Application.Substitute(OldComment, COMMENT_HEAD, "") & vbNewLine _
& Sheet2.Range(.Address) & " " & strDate & " " & Target.EntireRow.Range("b1")

I'd also suggest that you put the entire change routine inside an If Target.Cell.Count =1 Then .... End If. As is, pasting a range of cells will 1)change the data 2)replace any existing comments and 3) is not Un-Doable.

[davidw]

Thank you very much. This was exactly what i needed. And yes I see the conflict. Im going to mess with it some more this afternoon. Again thank you for your help.

[davidw]

I guess I spoke a little too early. I put the if statement in after the target declaration but it doesnt like it. I put it just before the with statement but it doesnt change anything. Where do i put it?

[mikerickson]

This modification of Sheet1's Change event worked for me. Unless a user might be using a Mac, you could un-replace the Application.Substitute with Replace. (Mac does not support VB's Replace function.)Private Sub Worksheet_Change(ByVal Target As Range)
Const COMMENT_HEAD As String = "PREVIOUS VALUES BELOW"
'====================================
Dim strDate As String
Dim strTime As String
strDate = date
strTime = Time
Dim OldComment
'====================================
With Target
If .Cells.Count = 1 Then
'//clearing more than one cell causes an error which will allow you to delete
On Error Resume Next
'//get the previous value when value changes
OldComment = .Comment.Text
'//(it willnot overwrite an existing comment but will add after)
.ClearComments
On Error GoTo 0

.AddComment
.Comment.Visible = False
.Comment.Text Text:=COMMENT_HEAD & Application.Substitute(OldComment, COMMENT_HEAD, "") & vbNewLine _
& Sheet2.Range(.Address) & " " & strDate & " " & .EntireRow.Range("B1")
End If
End With
Application.Run "'03-27-09 comment program to track changes.xls'!Comments_AutoSize"
End Sub

[mdmackillop]

You can store the target number as a variable which saves using sheet2

Dim tmpVal As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'copy previous value to another sheet
'create another worksheet to put data on and hide it
'Save owner name
tmpVal = Target.Text
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Const COMMENT_HEAD As String = "PREVIOUS VALUES BELOW"
'====================================
Dim strDate As String
Dim strTime As String
strDate = date
strTime = Time
Dim OldComment
'====================================
With Target
If .Cells.Count = 1 Then
'//clearing more than one cell causes an error which will allow you to delete
On Error Resume Next
'//get the previous value when value changes
OldComment = .Comment.Text
'//(it willnot overwrite an existing comment but will add after)
.ClearComments
On Error GoTo 0

.AddComment
.Comment.Visible = False
.Comment.Text Text:=COMMENT_HEAD & Application.Substitute(OldComment, COMMENT_HEAD, "") & vbNewLine _
& tmpVal & " " & strDate & " " & .EntireRow.Range("B1")
End If
End With
Application.Run "'03-27-09 comment program to track changes.xls'!Comments_AutoSize"
End Sub

[davidw]

Im not very good at this. No matter what Im not allowed to undo anything.

[mdmackillop]

If you mean undo last action, that option is not available with any VBA code

[davidw]

no not that. I mean when i manually cut and paste or copy, I cant undo.

[davidw]

Never mind. The macro runs everytime the sheet detects a change. I forget sometimes. lol

[mdmackillop]

Using this code in the event sub can limit its execution to certain columns.
If Target.Column <> 3 Then Exit Sub

You could also give yourself an "escape"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
'Get out
If MsgBox("Run code?", vbYesNo) = vbNo Then Exit Sub
'Code proceeds
MsgBox "code running"
End Sub

[davidw]

how do i put solved.... Thanks again. Works like a champ...

[mdmackillop]

Happy it's working!
You mark a thread Solved using the Thread Tools dropdown.