Worksheet_Change: Conditionally keeping previously entered value
Posted by Bjorn Skogseth on May 15, 2001 11:36 AM
Let's say that I want to restrict the value in cell A2 so that it cannot be higher than the value in A1. (And the same for B2 and B1, and so on - note that A1, B1, etc can have different values.)
I can do this by using the Worksheet_Change event and testing whether the newly entered value in A2 is outside the allowed range, ie. higher than A1. However, how do I keep the previously entered value in A2 in that case? I don't want to set A2=A1 (that's what I'm doing now), it would be better to keep the old value.
The Target As Range variable doesn't seem to contain any information on what the previous value in the changed cell was. Anyone know?
Posted by cpod on May 15, 2001 12:30 PM
You could use Undo:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 1 Then
If Target.Offset(-1, 0) < Target Then Application.Undo
End If
End Sub
Posted by Dax on May 15, 2001 12:38 PM
Hello,
You could use data validation rather than VBA code. For example, if you choose Data, Validation then choose Custom you could enter a formula such as =C3<C2 then copy this to all cells in your range. The user will then not be able to enter a value in cell D5 that is higher than the value in cell D4 plus the old cell value will be retained if it's invalid.
HTH,
Dax.
Posted by Dax on May 15, 2001 12:39 PM
The formula came out wrong on the post. Should be "=C3<C4".
Posted by Dax on May 15, 2001 12:42 PM
Not posting properly.....
Hi,
For some reason my suggestion is fine in the entry box but comes out incorrectly in the actual post. Probably because I'm using the less than operator.
Laters,
Dax.
Posted by Mark W. on May 15, 2001 12:47 PM
Re: Not posting properly.....Here's a workaround
This web site confuses a less than sign with the
start of a tag. I've found that placing spaces
on either side of the operator like so... =C3 < C4
helps avoid this difficulty. : )
Posted by Bjorn Skogseth on May 15, 2001 12:55 PM
Great! That was probably easy for you VB-pros (I'm normally a Java programmer myself), but I was 'locked in' thinking that the Target variable should contain the necessary info :)
Anyway, it works nicely, thanks!
Posted by Mark W. on May 15, 2001 1:05 PM
Re-Post with just my tip...
This web site confuses a less than sign with the
start of a tag. I've found that placing spaces
on both sides of the operator like so... =C3 < C4
helps avoid this difficulty. : )
