PDA

View Full Version : Sleeper: Capture old value of cell before change!



agarwaldvk
06-27-2013, 02:59 PM
Hi Everyone


I have a spreadsheet where I have a validation list (not a combo box or list control) in a particular column.

What I am looking to be able to do is to run a macro when the drop down arrow of the validation list is selected - ideally not when one of the values in the list has been selected. In other words, not after the cell value has been changed. I need to be able to capture the cell value before it was changed.

I have tried the ...selectionChange event - this will not work as in my case, as the cell that I want to change the value of might be the active cell at that time - and hence will not trigger the ...selectionChange event as there wouldn't be a need to change the selection to the cell that I need to change the value of.

Essentially, I am looking for the equivalent of the...click event of a control for this validation list.


Any suggestions please!



Best regards



Deepak Agarwal

SamT
06-27-2013, 04:11 PM
Change Event?
Private Sub Worksheet_Change(ByVal Target As Range)

Below is the Worksheet equivalent of Workbooks' Property Get and Let Procedures. The Selection change event is triggered all the time but you test for certain parameters.

Sometime when the cell held the old value it was selected. The only time it would already be selected after the old value was entered is while that value is actually the new value. The situation you are worried about only occurs when; A: The validation list is used, B: Without ever leaving the cell, you use the validation list to enter a new value, and, C: You need the previously selected, but totally unused, value stored


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ValidatingRange As Range
Set ValidatingRange = Range("A1", "B2:B4", "C5")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, ValidatingRange) is Nothing then Exit Sub
OldVal = Target.Value
End Sub

Private Function GetOldVal() As Variant
GetOldVal = OldVal
End Function

Private Function OldVal(Optional ReplaceVal As Variant) As Variant
Static Val
If ReplaceVal = "" Then
OldVal = Val
Else
Val = ReplaceVal
End If
End Function
If you need to store more than one cell's values, you will need a Collection of Cell addresses and pass the address and value of the OldValue you need or store. If that is the case, I would use different code flow logic.

Declare the Collection as a Global Variable in the ThisWorkbook code, and use the Workbook Open event to initialise the collection with all the relevant addresses. Then you can test the Target.Address against the Collection's Index and use a variation of the OldVal function above to assign the OldValue to the collections Address Index

p45cal
06-27-2013, 04:15 PM
Check out the Undo method in Help.
Play about with the likes of:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
newvalue = Range("A1").Value
Application.Undo
oldvalue = Range("A1").Value
Range("A1").Value = newvalue
Application.EnableEvents = True
MsgBox "new value " & newvalue & vbLf & "old value " & oldvalue
End Sub


If there's only a very few cells you want to capture the old value of, check out Static Dimming, or hold the old value in a global variable.

agarwaldvk
06-27-2013, 06:10 PM
Hi SamT & p45cal


Thanks for your responses - however, I don't get them.

Could you please walk me through them - particularly your code SamT, too hard for me to understand!. What's the idea of "Intersect()" and where do I need to call the functions from etc.

p45cal
Where do we get the new and old values that are different?

Got it now - no worries! Thanks!

If this is any help, I have attached the spreadsheet that I am looking at using this functionality in!

The idea is that in the "Test Summary" worksheet, in column("H"), once the value in cell in any row is "Failed", this value subsequently cannot be changed to anything else. This is because it is intended to build a functionality where a new record (essentially a copy of this record) would be created which can then show the details of the subsequent testing). This way the history of the testing can be maintained rather than overwriting the status on the same record. So that is why I need to know what the value in the cells in any row was before it was attempted to be changed.

Sorry about this but may be I am not as clever!!!!!!!!



Best regards


Deepak

SamT
06-28-2013, 02:42 PM
Deepak,

Your last post puts a different look on what you need.

First I put these lines in module1

Const WorkSheetDesignMode As Boolean = False
'In order to work on any protected Values columns, you must set this contant = "True"
'The line "If WorkSheetDesignMode Then Exit Sub"
'should be placed at the top of all procedures that work to prevent
'a User from modifying a ProtectedValue.

The reason is that if you want to ever need to modify column "H", you need a way to prevent the subs from running.

Then I put a User Defined Type and a Global Variable of that Type also in Module1.

'I put these in module1 so that in the future, you can use
'the Type "ProtectedValues" for other sheets and Ranges
Type ProtectedValues
'Can be used for many variables, Ranges, values
pvAddress As String
pvValue As Variant
End Type
Static pvSummaryFailed As ProtectedValues
'You can declare any variable as ProtectedValues and it will
'hold two values: Address and Value. See Code on Sheet1

Note the "pv" prefix. It stands for ProtectedValues and allows easy recognition and the use of Key words as the names, (Address and Value.)

The need to prevent changing a "FAILED" requires the use of two functions, one to hold the value and one to replace it if it is changed. I wrote these with the idea that you might need to expand the capabilities later.

The Sheet_SelectionCchange stores the adress and value of the cell when it is selected. The Sheet_Change puts "Failed" back if it is changed. Because I wrote them for easy maintenance, I never used hardcoded values, always variables.

These procedures are in the "Tests Summary" code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'This sub stores the address and value of the selected cell in Column "H"
Dim ChkValue As String
ChkValue = "FAILED"
If WorkSheetDesignMode Then Exit Sub 'See Module1
'Test if Selection includes Column "H"
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
'Check if the Cell says "FAILED"
If Not (UCase(Target.Value) = ChkValue) Then Exit Sub
'If the sub is still running, then one cell in "H" was selected
'and its value is "FAILED"
'Now we'll save the address and value
With pvSummaryFailed
.pvAddress = Target.Address
.pvValue = ChkValue
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'This sub checks the address of the changed cell in Column "H"
'to see if it is the protected cell. If so it changes the value
'back to "Failed"
If WorkSheetDesignMode Then Exit Sub 'See Module1
'First run the usual checks
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
'Now check if this is the Protected cell
If Target.Address = pvSummaryFailed.pvAddress Then
Target.Value = pvSummaryFailed.pvValue
End If
End Sub


.this tests ok on my system. I leave it to you to fix the value of "FAILED" to "Failed".

Please note that I had to remove the References to make it work for me.

snb
06-29-2013, 08:39 AM
As soon as you save any value in that cell somewhere else : that's the 'old value'.
Use the worksheeet_change event.