Worksheet Change event
 

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

Worksheet Change event
 

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

Worksheet Change event
 

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

Worksheet Change event
 

The Application.Undo worked. Thank you very much!


--
LAF
------------------------------------------------------------------------
LAF's Profile: http://www.excelforum.com/member.php...fo&userid=9656
View this thread: http://www.excelforum.com/showthread...hreadid=497520