excel 2003 - vba - remember old cell value before change

[silentbuddha]

Hi,

I am not sure how to write the vba code to retain the old cell value before user makes a change. Thank you

here is my code so far :

*************************************************

Private Sub Worksheet_Change(ByVal Target As Range)
'This procedure will check whether the user made a change in specific cell

Dim cellOldValue As Variant

Dim interSectRange As Range

'set the range
Set interSectRange = Range("B16:B1430")

Application.EnableEvents = False

'Check whether active cell is within a specified range, if not then do nothing
If Intersect(Target, interSectRange) Is Nothing Then

' code to handle that the active cell is not within the right range
MsgBox "Active Cell not in Range!"
Application.EnableEvents = True
Exit Sub
Else

' code to handle when the active cell is within the right range
MsgBox "Active Cell In Range!"
Application.EnableEvents = True
'Exit Sub
End If

'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

'Ensure target cell value is a number
If IsNumeric(Target) Then
MsgBox "Value entered is a number!"
Application.EnableEvents = True
'Exit Sub
Else
MsgBox "value entered is not a number, please verify!"
Application.EnableEvents = True
Exit Sub

End If


'Check whether target cell address is valid before calling swapCells subroutine
If (Target.Row - Range("B16").Row) Mod 14 = 0 Then

MsgBox "The cell you have selected is allowed!"

'Call swapcells
Application.EnableEvents = True
Exit Sub
Else

MsgBox "the cell you have selected is not allowed!"
Application.EnableEvents = True
Exit Sub

End If

Application.EnableEvents = True
End Sub

 

[RoryA]

You would either have to store the value(s) in a variable using the SelectionChange event, or store the new values, then use Application.Undo to return to the old values and then use whichever is appropriate.

 

[9ballpimp]

Just copy this code into whatever Sheet VB Object you are using (Sheet1, Sheet2, etc.).

Dim vOldVal 'Must be at top of module


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

This will assign the old value to the vOldVal variable. You can also use "Target" to get the cell address of the change, etc. Hope that helps!

Troy

 

[silentbuddha]

Hi Troy,

that subroutine worked like a charm....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

still trying to figure out what it does exactly...

 

[9ballpimp]

It's triggered everytime there is any change on that particular sheet.

Troy

 

[silentbuddha]

Hi All,

Thanks for the guidance....all worked like a charm