Worksheet Change event

[LAF]

I have a range on one of my worksheets that I use the Worksheet change event for. The range is a column of about 200 rows where the user can enter percentages. Right now I have code in there that makes sure they don't enter a percentage less than a default one that has been already set. If they do enter one that is less, then a message is displayed and the value of that cell is set back to the default. The user is allowed to increase the percentage. However, if the user increases it and then goes back to the cell to try to make it lower than the default, then it sets it back to the default, not the higher value they had there previously. For example, if the default is 4% and then I increase it to 5% and then try to lower it to 3%, it will set it back to the 4% instead of 5%. Is there a way to set it back to what it previously had in there instead of setting it to the default.
Also, another thing I noticed is that it calls the worksheet_change event even when I type in the same number that was there. For example, if the number was 4% and I type in 4% again, it calls that event. Is there a way to avoid that?

[Stephen Bullen]

Hi Laf,

> However, if the user increases it
> and then goes back to the cell to try to make it lower than the
> default, then it sets it back to the default, not the higher value they
> had there previously. For example, if the default is 4% and then I
> increase it to 5% and then try to lower it to 3%, it will set it back
> to the 4% instead of 5%. Is there a way to set it back to what it
> previously had in there instead of setting it to the default.

One way is to use the Selection_Change event to store the original number
in a module-level variable and set the cell back to that (assuming it is
above the threshold level).

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

[Dave Peterson]

Another way is to use Application.undo in your code.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Intersect(Target, Me.Range("b:b")) Is Nothing Then
Exit Sub
End If

On Error GoTo errHandler:

If Target.Value > 1 Then
With Application
.EnableEvents = False
.Undo
End With
MsgBox "no, nope, na, na!"
End If

errHandler:
Application.EnableEvents = True
End Sub

LAF wrote:
>
> I have a range on one of my worksheets that I use the Worksheet change
> event for. The range is a column of about 200 rows where the user can
> enter percentages. Right now I have code in there that makes sure they
> don't enter a percentage less than a default one that has been already
> set. If they do enter one that is less, then a message is displayed
> and the value of that cell is set back to the default. The user is
> allowed to increase the percentage. However, if the user increases it
> and then goes back to the cell to try to make it lower than the
> default, then it sets it back to the default, not the higher value they
> had there previously. For example, if the default is 4% and then I
> increase it to 5% and then try to lower it to 3%, it will set it back
> to the 4% instead of 5%. Is there a way to set it back to what it
> previously had in there instead of setting it to the default.
> Also, another thing I noticed is that it calls the worksheet_change
> event even when I type in the same number that was there. For example,
> if the number was 4% and I type in 4% again, it calls that event. Is
> there a way to avoid that?
>
> --
> LAF
> ------------------------------------------------------------------------
> LAF's Profile: http://www.excelforum.com/member.php...fo&userid=9656
> View this thread: http://www.excelforum.com/showthread...hreadid=497520

--

Dave Peterson

[LAF]

The Application.Undo worked. Thank you very much!