Stop the loop in Worksheet_Change

[sue]

Hi all,

I want to have my workbook validate entries and return users to
incomplete input, but when I use the "Worksheet_Change" (which is what I
think is appropriate) I get caught in an endless updating loop. This is
because I want to send the user back to the previous cell AND empty the
current cell. The code unhides the first system worksheet and then
copies it twice as system names are entered.

I assume that changing any of the cell's values within this event
retriggers it, thus the endless looping. My basic question is:

Is there a way to break out of this and still perform these checks?

My code looks like this:

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Private Sub Worksheet_Change(ByVal Target As Range)
' this sub will change the name of the alternative system worksheets
' or add or delete their respective sheets as needed

On Error Resume Next

Application.DisplayAlerts = False ' hide any error or confirmation messages

Select Case Target.Address ' these addresses must be absolute addresses
Case "$B$18"
If Me.Range("B18").Value = "" Then
Worksheets(2).Visible = xlHidden
Else
Worksheets(2).Visible = True
Worksheets(2).Name = Me.Range("B18").Value & " - System 1"
Me.Range("C18").Activate
End If
Case "$C$18"
If Range("B18").Value = "" Then ' if there isn't a value in
the first cell
MsgBox "Please enter a name for the FIRST Proposed
System!", vbOKOnly, "First system not defined!"
Range("B18").Activate
If Worksheets(" - System 2").Visible = True Then
Worksheets(" - System 2").Delete
End If
Range("C18").Value = ""
Range("D18").Value = ""
Application.DisplayAlerts = True
Exit Sub
Else
Worksheets(2).Copy After:=Worksheets(2)
Worksheets(3).Name = Me.Range("C18").Value & " - System 2"
Me.Range("D18").Activate
End If
Case "$D$18"
If Me.Range("B18").Value = "" Then ' if there isn't a value in
the first cell
MsgBox "Please enter a name for the FIRST Proposed
System!", vbOKOnly, "Second system not defined!"
Me.Range("B18").Activate
If Worksheets(" - System 3").Visible = True Then
Worksheets(" - System 3").Delete
End If
Range("D18").Value = ""
Application.DisplayAlerts = True
Exit Sub
ElseIf Me.Range("C18").Value = "" Then ' if there isn't a
value in the second cell
MsgBox "Please enter a name for the SECOND Proposed
System!", vbOKOnly, "Second system not defined!"
Me.Range("C18").Activate
Range("D18").Value = ""
Application.DisplayAlerts = True
Exit Sub
Else ' this must be an entry into the third system's cell...good
Worksheets(2).Copy After:=Worksheets(3)
Worksheets(3).Name = Me.Range("D18").Value & " - System 3"
End If

End Select

Application.DisplayAlerts = True ' turn back on any error or
confirmation messages


End Sub

Thank you in advance for any assistance,

Susan

 

[Frank Kabel]

Hi
add the line
Application.enableevents = false at the beginning

and the line
Application.enableevents = true at the end

 

[sue]

Hi
add the line
Application.enableevents = false at the beginning

and the line
Application.enableevents = true at the end

Thanks,

That's all I needed!

Susan