Undo Acting Weird

In the example below, the statement following "Else" is executed before the
statements following "Application.Undo".
Can someone explain why, and the proper way to do this?
Thanks,
Bernie

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRow, NumberRows, CellOnOpen, CurrentRow, Tier
If Target.Column <> 1 Then If Target.Column <> 3 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
ActiveSheet.Unprotect
CurrentRow = ActiveCell.Row
If Target.Column = 1 And CurrentRow = 1 And Selection.Value = "" Then
Application.Undo
MsgBox ("You may not delete this row, but you may modify as
necessary")
Cells(CurrentRow, 1).Select
Exit Sub
Else
MsgBox ("Why is this being executed when I DELETE cell A1?")
End If
End Sub

Your Undo triggers a new Worksheet_Change so it runs recursively from within
your sub (it calls itself). You can see this if you put as the very first
line in the sub MsgBox "Worksheet_Change". You will see the message twice
before your other messagebox.
One way around this is to use a static variable to tell it to skip the
instructions the 2nd time through:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRow, NumberRows, CellOnOpen, CurrentRow, Tier
Static Ignore As Boolean
If Not (Ignore) Then
If Target.Column <> 1 Then If Target.Column <> 3 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
ActiveSheet.Unprotect
CurrentRow = ActiveCell.Row
If Target.Column = 1 And CurrentRow = 1 And Selection.Value = "" Then
Ignore = True
Application.Undo
MsgBox "You may not delete this row, but you may modify asnecessary "
Cells(CurrentRow, 1).Select
Exit Sub
Else
MsgBox ("Why is this being executed when I DELETE cell A1?")
End If
End If
Ignore = False
End Sub
--
- K Dales


"bw" wrote:

> In the example below, the statement following "Else" is executed before the
> statements following "Application.Undo".
> Can someone explain why, and the proper way to do this?
> Thanks,
> Bernie
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim TargetRow, NumberRows, CellOnOpen, CurrentRow, Tier
> If Target.Column <> 1 Then If Target.Column <> 3 Then Exit Sub
> If Target.Cells.Count > 1 Then Exit Sub
> ActiveSheet.Unprotect
> CurrentRow = ActiveCell.Row
> If Target.Column = 1 And CurrentRow = 1 And Selection.Value = "" Then
> Application.Undo
> MsgBox ("You may not delete this row, but you may modify as
> necessary")
> Cells(CurrentRow, 1).Select
> Exit Sub
> Else
> MsgBox ("Why is this being executed when I DELETE cell A1?")
> End If
> End Sub
>
>
>

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetRow, NumberRows, CellOnOpen, CurrentRow, Tier
If Target.Column <> 1 Then If Target.Column <> 3 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
ActiveSheet.Unprotect
CurrentRow = ActiveCell.Row
If Target.Column = 1 And CurrentRow = 1 And Selection.Value = "" Then
Application.EnableEvents = False
Application.Undo
MsgBox ("You may not delete this row, but you may modify as
necessary")
Cells(CurrentRow, 1).Select
Application.EnableEvents = True
Exit Sub
Else
MsgBox ("Why is this being executed when I DELETE cell A1?")
End If
End Sub

--
Regards,
Tom Ogilvy


"bw" <[email protected]> wrote in message
news:[email protected]...
> In the example below, the statement following "Else" is executed before
the
> statements following "Application.Undo".
> Can someone explain why, and the proper way to do this?
> Thanks,
> Bernie
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim TargetRow, NumberRows, CellOnOpen, CurrentRow, Tier
> If Target.Column <> 1 Then If Target.Column <> 3 Then Exit Sub
> If Target.Cells.Count > 1 Then Exit Sub
> ActiveSheet.Unprotect
> CurrentRow = ActiveCell.Row
> If Target.Column = 1 And CurrentRow = 1 And Selection.Value = "" Then
> Application.Undo
> MsgBox ("You may not delete this row, but you may modify as
> necessary")
> Cells(CurrentRow, 1).Select
> Exit Sub
> Else
> MsgBox ("Why is this being executed when I DELETE cell A1?")
> End If
> End Sub
>
>

Thank you very much Tom and K!
It now works like a charm.
I appreciate your help.
Bernie

"bw" <[email protected]> wrote in message
news:[email protected]...
> In the example below, the statement following "Else" is executed before
> the statements following "Application.Undo".
> Can someone explain why, and the proper way to do this?
> Thanks,
> Bernie
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim TargetRow, NumberRows, CellOnOpen, CurrentRow, Tier
> If Target.Column <> 1 Then If Target.Column <> 3 Then Exit Sub
> If Target.Cells.Count > 1 Then Exit Sub
> ActiveSheet.Unprotect
> CurrentRow = ActiveCell.Row
> If Target.Column = 1 And CurrentRow = 1 And Selection.Value = "" Then
> Application.Undo
> MsgBox ("You may not delete this row, but you may modify as
> necessary")
> Cells(CurrentRow, 1).Select
> Exit Sub
> Else
> MsgBox ("Why is this being executed when I DELETE cell A1?")
> End If
> End Sub
>
>