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

Worksheet_Change and Application.undo
 

putting

Application.EnableEvents = False

before application.undo seems to be working for me.


Mangesh



"Mangesh Yadav" wrote in message
...
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

Worksheet_Change and Application.undo
 

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" wrote in message
...
putting

Application.EnableEvents = False

before application.undo seems to be working for me.


Mangesh



"Mangesh Yadav" wrote in message
...
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

Worksheet_Change and Application.undo
 

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" wrote in message
...
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" wrote in message
...
putting

Application.EnableEvents = False

before application.undo seems to be working for me.


Mangesh



"Mangesh Yadav" wrote in message
...
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