Worksheet_Change and Application.undo

[Mangesh Yadav]

I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$83" And Target.Value >= 1 And Target.Value
<= 4 Then
' do something
Else
Application.Undo
End If
End Sub

To start with, lets say F83 has 3. Now my problem is, if I enter say 5 in
F83, then the above code is triggered, and through the Else, Application
Undo is triggered, and 3 is entered again in F83. But this again triggers
the changes event.

My question: Can this second trigger be avoided somehow?

I don't want to use the Data > Validation, as the above thing is just a
small part of what I am trying to do.

 

[Mangesh Yadav]

putting

Application.EnableEvents = False

before application.undo seems to be working for me.


Mangesh

 

[Bob Phillips]

Mangesh,

A couple of points

make sure you reset
and add error trappin g to rest on error

Like so

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$F$83" And .Value >= 1 And .Value <= 4 Then
'do your stuff
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

 

[Mangesh Yadav]

Hi Bob,

Thanks. Have taken care of both the points. I was presuming that I already
had the EnableEvents 'off', and still it was not working. Anyway, thanks.

Mangesh