Worksheet_Change and Application.undo

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
------------------------------------------
Office XP & Windows XP

putting

Application.EnableEvents = False

before application.undo seems to be working for me.


Mangesh



"Mangesh Yadav" <[email protected]> wrote in message
news:[email protected]...
> 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
> ------------------------------------------
> Office XP & Windows XP
>
>

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

--
HTH

Bob Phillips

"Mangesh Yadav" <[email protected]> wrote in message
news:[email protected]...
> putting
>
> Application.EnableEvents = False
>
> before application.undo seems to be working for me.
>
>
> Mangesh
>
>
>
> "Mangesh Yadav" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > ------------------------------------------
> > Office XP & Windows XP
> >
> >
>
>

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




"Bob Phillips" <[email protected]> wrote in message
news:#[email protected]...
> 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
>
> --
> HTH
>
> Bob Phillips
>
> "Mangesh Yadav" <[email protected]> wrote in message
> news:[email protected]...
> > putting
> >
> > Application.EnableEvents = False
> >
> > before application.undo seems to be working for me.
> >
> >
> > Mangesh
> >
> >
> >
> > "Mangesh Yadav" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > ------------------------------------------
> > > Office XP & Windows XP
> > >
> > >
> >
> >
>
>