jasman
10-30-2010, 07:55 AM
Hi. I have a spreadsheet which contains product code information. I need to create a macro that when a particular cell value is changed, a form shows asking for reason for chnage and then inputs that information into another cell in the following manner:
"current date"-"cell name changed"-"old value"-"new value"-"username"
I have all of it working except for the "old value". I cant get the previous value of the cell to come up.
Below is the code ive used. Can you help?
Thanks in advance....
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count > 1 Then GoTo Label1
If Target.Value = old_value Then GoTo Label1
' TECHNICAL ASSUMPTION CHANGED
If DataSheet.Cells(2, Target.Column).Value = "TECHNICAL ASSUMPTION" Then
to_be_changed = True
tech_update_comment = get_tech_update_comment
If tech_update_comment = "Cancel" Then
GoTo Label1
ElseIf tech_update_comment = "" Then
text_to_add = Left(Now(), 10) & " - Assumption Changed - " & FinanceStuff.Range("c18").Value
End If
End If
If to_be_changed Then
old_history_value = ActiveSheet.Cells(Target.Row, 3).Value
If old_history_value <> "" Then
new_history_value = old_history_value & text_to_add
Else
new_history_value = text_to_add
End If
If new_history_value <> old_history_value Then
ActiveSheet.Cells(Target.Row, 3).Value = new_history_value
ActiveSheet.Cells(Target.Row, 3).WrapText = False
ActiveSheet.Cells(Target.Row, 3).ShrinkToFit = True
ActiveSheet.Cells(Target.Row, 4).NumberFormat = "@"
ActiveSheet.Cells(Target.Row, 4).Value = Left(Now(), 10)
End If
End If
Label1:
Application.EnableEvents = True
End Sub
"current date"-"cell name changed"-"old value"-"new value"-"username"
I have all of it working except for the "old value". I cant get the previous value of the cell to come up.
Below is the code ive used. Can you help?
Thanks in advance....
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count > 1 Then GoTo Label1
If Target.Value = old_value Then GoTo Label1
' TECHNICAL ASSUMPTION CHANGED
If DataSheet.Cells(2, Target.Column).Value = "TECHNICAL ASSUMPTION" Then
to_be_changed = True
tech_update_comment = get_tech_update_comment
If tech_update_comment = "Cancel" Then
GoTo Label1
ElseIf tech_update_comment = "" Then
text_to_add = Left(Now(), 10) & " - Assumption Changed - " & FinanceStuff.Range("c18").Value
End If
End If
If to_be_changed Then
old_history_value = ActiveSheet.Cells(Target.Row, 3).Value
If old_history_value <> "" Then
new_history_value = old_history_value & text_to_add
Else
new_history_value = text_to_add
End If
If new_history_value <> old_history_value Then
ActiveSheet.Cells(Target.Row, 3).Value = new_history_value
ActiveSheet.Cells(Target.Row, 3).WrapText = False
ActiveSheet.Cells(Target.Row, 3).ShrinkToFit = True
ActiveSheet.Cells(Target.Row, 4).NumberFormat = "@"
ActiveSheet.Cells(Target.Row, 4).Value = Left(Now(), 10)
End If
End If
Label1:
Application.EnableEvents = True
End Sub