-
oldvalue help
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....
[VBA]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
[/VBA]
-
Hi Jasman
Welcome to VBAX.
Use a SelectionChange event to set a global variable to the Target value. This will be Old_Value
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
-
A simple example
[VBA]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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules