• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recalling a cell's previous value (VBA)

idahodave

New Member
Hello. I have a macro that is triggered when one of three options in a dropdown is selected. The first option changes a cell value to zero. I need to figure out how to bring the last value that was in the cell the last time one of the two other options was selected. For example if the cell value was 50 when option B was chosen, then when it changed to option C, the value of that cell remained 50. Then when option A was chosen, the value changed to zero. I need a way to change it back to 50 (or whatever the value was) when options B or C are chosen again. I know this is as clear as mud but I'd appreciate any help. Here's the code I've been trying:

[pre]
Code:
Sub ChangeIt()
'
' ChangeIt Macro
'

Range("C20").Select
Selection.Copy
Range("I54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

If Range("C3") = "Option A" Then
Range("C20").Select
ActiveCell.FormulaR1C1 = "0"
End If

If Range("C3") <> "Option A" Then
Range("I54").Select
Selection.Copy
Range("C20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

'
End Sub
[/pre]
 
Hi, idahodave!

Tried using an auxiliary variable to store the previous value? Or even a cell, so as to have it after closing and reopening the workbook.

Regards!
 
Thanks for the reply, SirJB7! Unfortunately, my VBA skills are more basic than that so I'm not sure how to set up an auxiliary variable.
 
Right click on sheet tab, view code, paste this in. Uses a public variable to store the value between macro runs. Note that macro runs automatically when dropdown is changed.

[pre]
Code:
Public OldValue As Double

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DropCell As Range
Dim ValueCell As Range
Dim NewValue As Double
Dim OldChoice As String
Dim NewChoice As String

Set DropCell = Me.Range("C3")
Set ValueCell = Me.Range("C20")

If Intersect(DropCell, Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

If UCase(DropCell.Value) = "OPTION A" Then
'Store the previous value into the public variable
OldValue = ValueCell.Value
ValueCell = 0
Else
'Store the new dropdown choice
NewChoice = DropCell.Value
Application.Undo
'If was previously B or C, then don't change
If UCase(DropCell.Value) = "OPTION A" Then
ValueCell = OldValue
End If
DropCell.Value = NewChoice

End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
If value needs to be preserved between opening/closing the workbook, this alternate will work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DropCell As Range
Dim ValueCell As Range
Dim OldValue As Range
Dim NewValue As Double
Dim OldChoice As String
Dim NewChoice As String

Set DropCell = Me.Range("C3")
Set ValueCell = Me.Range("C20")

'Make this any cell that is out of the way
Set OldValue = Me.Range("ZZ1")

If Intersect(DropCell, Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False

If UCase(DropCell.Value) = "OPTION A" Then
'Store the previous value into the public variable
OldValue = ValueCell.Value
ValueCell = 0
Else
'Store the new dropdown choice
NewChoice = DropCell.Value
Application.Undo
'If was previously B or C, then don't change
If UCase(DropCell.Value) = "OPTION A" Then
ValueCell = OldValue
End If
DropCell.Value = NewChoice

End If

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/pre]
 
That would be great, SirJB7! Here's a link:


https://docs.google.com/open?id=0BwLaTPN4vlPfZWViRUVobjh3R2s


In the meantime, I'll look into Luke's suggestion - thanks, Luke!
 
Hi, idahodave!


Being short of time for analyzing Luke M's proposal, I added an If to your code and I think it works. Please check it. I'll come back tomorrow to try Luke M's code (which I discard it runs fine, as usual).


The link:

http://dl.dropbox.com/u/60558749/Recalling%20a%20cell%27s%20previous%20value%20%28VBA%29%20-%20ChangeIt%20Sample%20%28for%20idahodave%20at%20chandoo.org%29.xlsm


Regards!
 
Hi, idahodave!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted. Still pending the other option.

Regards!
 
Back
Top