|
Code does a continous loop
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 |
Code does a continous loop
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" wrote in message ... 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 |
Code does a continous loop
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 |
Code does a continous loop
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" wrote in message ... 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 |
Code does a continous loop
OK, yet one more DUH!!!
Failed to grasp the first response. Regards, Howard "L. Howard Kittle" wrote in message ... 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 |
| All times are GMT +1. The time now is 12:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com