Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Code does a continous loop
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 698
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 698
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,119
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 698
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 698
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Loop for VBA code? paulinoluciano Excel Worksheet Functions 5 December 28th 05 01:30 PM
How to Loop some code Phil Osman Excel Discussion (Misc queries) 2 August 19th 05 11:14 AM
How to get my code to loop Dominique Feteau[_2_] Excel Programming 5 December 17th 04 01:35 PM
Help with loop code... gaba Excel Programming 1 October 20th 04 01:37 PM
VBE code in a loop loloflores Excel Programming 0 April 30th 04 12:28 PM


All times are GMT +1. The time now is 11:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement