oldvalue help

[jasman]

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

[mdmackillop]

Hi Jasman
Welcome to VBAX.
Use a SelectionChange event to set a global variable to the Target value. This will be Old_Value

[jasman]

Hi. Not sure how to do that but I seem to have got it working with:

Dim newValue As Variant
Dim prevValue As Variant
newValue = Target.Value
Application.Undo
prevValue = Target.Value
Target.Value = newValue

[mdmackillop]

A simple example

Option Explicit

Dim Old_Value

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Old_Value = Target
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target <> Old_Value Then
MsgBox "Changed"
End If
End Sub