Code does a continous loop

[L. Howard Kittle]

Hello Excel users and experts,
Excel 2002

In the first If statement when i = 11 it statement works fine as long as I
have ' Target.Value = "" out of the code.
If I remove the ' from Target.Value = "" and run the macro it goes ino a
continous loop with the message box displayed. I have to crash out with
Ctrl -Alt - Del. Am I looking right past something?

Thanks for any help you can offer.
Regards,
Howard

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer

i = Application.WorksheetFunction. _
CountA(Worksheets("Sales Order") _
.Range("A17:A27"))

If i = 11 Then
MsgBox "The sales order is full." & vbCr _
& vbCr & "Process a new order."
' Target.Value = ""
Application.DisplayAlerts = False
Exit Sub
End If

If Target.Column <> 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Target.Resize(1, 4).Select
If MsgBox(prompt:=" Post this selection to Order Sheet?", _
Buttons:=vbYesNo, _
Title:="Poster") = vbYes Then
Target.Resize(1, 4).Copy _
Sheets("Sales Order").Range("A27") _
.End(xlUp).Offset(1, 0)
Target.Value = ""
Application.DisplayAlerts = False
ElseIf vbNo Then
Target.Value = ""
Application.DisplayAlerts = False
End If
Target.Select

End Sub

[L. Howard Kittle]

Well, DUH!

It just hit me. I moved these two exit sub lines to the top of the code and
all is fine.

If Target.Column <> 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

Sorry for taking up space and time.

Regards,
Howard

"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hello Excel users and experts,
> Excel 2002
>
> In the first If statement when i = 11 it statement works fine as long as I
> have ' Target.Value = "" out of the code.
> If I remove the ' from Target.Value = "" and run the macro it goes ino a
> continous loop with the message box displayed. I have to crash out with
> Ctrl -Alt - Del. Am I looking right past something?
>
> Thanks for any help you can offer.
> Regards,
> Howard
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim i As Integer
>
> i = Application.WorksheetFunction. _
> CountA(Worksheets("Sales Order") _
> .Range("A17:A27"))
>
> If i = 11 Then
> MsgBox "The sales order is full." & vbCr _
> & vbCr & "Process a new order."
> ' Target.Value = ""
> Application.DisplayAlerts = False
> Exit Sub
> End If
>
> If Target.Column <> 1 Then Exit Sub
> If Target.Value = "" Then Exit Sub
> Target.Resize(1, 4).Select
> If MsgBox(prompt:=" Post this selection to Order Sheet?", _
> Buttons:=vbYesNo, _
> Title:="Poster") = vbYes Then
> Target.Resize(1, 4).Copy _
> Sheets("Sales Order").Range("A27") _
> .End(xlUp).Offset(1, 0)
> Target.Value = ""
> Application.DisplayAlerts = False
> ElseIf vbNo Then
> Target.Value = ""
> Application.DisplayAlerts = False
> End If
> Target.Select
>
> End Sub
>

[Jim Thomlinson]

You have a recursive call when you change the Target.value you call the
change function again and round round you go... You need to temporarily
disable the events somehtning like this (As with all application level
setting it is best to add an error handler to restore them in case of a
crash)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
on error goto errorhandler
application.enableevents = false

i = Application.WorksheetFunction. _
CountA(Worksheets("Sales Order") _
.Range("A17:A27"))

If i = 11 Then
MsgBox "The sales order is full." & vbCr _
& vbCr & "Process a new order."
' Target.Value = ""
Application.DisplayAlerts = False
Exit Sub
End If

If Target.Column <> 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Target.Resize(1, 4).Select
If MsgBox(prompt:=" Post this selection to Order Sheet?", _
Buttons:=vbYesNo, _
Title:="Poster") = vbYes Then
Target.Resize(1, 4).Copy _
Sheets("Sales Order").Range("A27") _
.End(xlUp).Offset(1, 0)
Target.Value = ""
Application.DisplayAlerts = False
ElseIf vbNo Then
Target.Value = ""
Application.DisplayAlerts = False
End If
Target.Select

ErrorHandler:
application.enableevents = true
application.displayalerts = true
End Sub

--
HTH...

Jim Thomlinson


"L. Howard Kittle" wrote:

> Hello Excel users and experts,
> Excel 2002
>
> In the first If statement when i = 11 it statement works fine as long as I
> have ' Target.Value = "" out of the code.
> If I remove the ' from Target.Value = "" and run the macro it goes ino a
> continous loop with the message box displayed. I have to crash out with
> Ctrl -Alt - Del. Am I looking right past something?
>
> Thanks for any help you can offer.
> Regards,
> Howard
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim i As Integer
>
> i = Application.WorksheetFunction. _
> CountA(Worksheets("Sales Order") _
> .Range("A17:A27"))
>
> If i = 11 Then
> MsgBox "The sales order is full." & vbCr _
> & vbCr & "Process a new order."
> ' Target.Value = ""
> Application.DisplayAlerts = False
> Exit Sub
> End If
>
> If Target.Column <> 1 Then Exit Sub
> If Target.Value = "" Then Exit Sub
> Target.Resize(1, 4).Select
> If MsgBox(prompt:=" Post this selection to Order Sheet?", _
> Buttons:=vbYesNo, _
> Title:="Poster") = vbYes Then
> Target.Resize(1, 4).Copy _
> Sheets("Sales Order").Range("A27") _
> .End(xlUp).Offset(1, 0)
> Target.Value = ""
> Application.DisplayAlerts = False
> ElseIf vbNo Then
> Target.Value = ""
> Application.DisplayAlerts = False
> End If
> Target.Select
>
> End Sub
>
>
>

[L. Howard Kittle]

Hi Jim,

Thanks for the info. Can you give me an example of an error handler for
this code? I'm pretty foggy on error handling.

Regards,
Howard

"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hello Excel users and experts,
> Excel 2002
>
> In the first If statement when i = 11 it statement works fine as long as I
> have ' Target.Value = "" out of the code.
> If I remove the ' from Target.Value = "" and run the macro it goes ino a
> continous loop with the message box displayed. I have to crash out with
> Ctrl -Alt - Del. Am I looking right past something?
>
> Thanks for any help you can offer.
> Regards,
> Howard
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim i As Integer
>
> i = Application.WorksheetFunction. _
> CountA(Worksheets("Sales Order") _
> .Range("A17:A27"))
>
> If i = 11 Then
> MsgBox "The sales order is full." & vbCr _
> & vbCr & "Process a new order."
> ' Target.Value = ""
> Application.DisplayAlerts = False
> Exit Sub
> End If
>
> If Target.Column <> 1 Then Exit Sub
> If Target.Value = "" Then Exit Sub
> Target.Resize(1, 4).Select
> If MsgBox(prompt:=" Post this selection to Order Sheet?", _
> Buttons:=vbYesNo, _
> Title:="Poster") = vbYes Then
> Target.Resize(1, 4).Copy _
> Sheets("Sales Order").Range("A27") _
> .End(xlUp).Offset(1, 0)
> Target.Value = ""
> Application.DisplayAlerts = False
> ElseIf vbNo Then
> Target.Value = ""
> Application.DisplayAlerts = False
> End If
> Target.Select
>
> End Sub
>

[L. Howard Kittle]

OK, yet one more DUH!!!

Failed to grasp the first response.

Regards,
Howard


"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hello Excel users and experts,
> Excel 2002
>
> In the first If statement when i = 11 it statement works fine as long as I
> have ' Target.Value = "" out of the code.
> If I remove the ' from Target.Value = "" and run the macro it goes ino a
> continous loop with the message box displayed. I have to crash out with
> Ctrl -Alt - Del. Am I looking right past something?
>
> Thanks for any help you can offer.
> Regards,
> Howard
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim i As Integer
>
> i = Application.WorksheetFunction. _
> CountA(Worksheets("Sales Order") _
> .Range("A17:A27"))
>
> If i = 11 Then
> MsgBox "The sales order is full." & vbCr _
> & vbCr & "Process a new order."
> ' Target.Value = ""
> Application.DisplayAlerts = False
> Exit Sub
> End If
>
> If Target.Column <> 1 Then Exit Sub
> If Target.Value = "" Then Exit Sub
> Target.Resize(1, 4).Select
> If MsgBox(prompt:=" Post this selection to Order Sheet?", _
> Buttons:=vbYesNo, _
> Title:="Poster") = vbYes Then
> Target.Resize(1, 4).Copy _
> Sheets("Sales Order").Range("A27") _
> .End(xlUp).Offset(1, 0)
> Target.Value = ""
> Application.DisplayAlerts = False
> ElseIf vbNo Then
> Target.Value = ""
> Application.DisplayAlerts = False
> End If
> Target.Select
>
> End Sub
>