Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping procedure calls userform; how to exit loop (via userform button)?
Hi all-
using Win2K, XL2003 I have a userform that pulls inconsistent records and allows the user match certain items to remove the inconsistent data. I've written it so it loops through each item in the workbook and presents them all to the user. Now that I'm testing it to see how it works, I realize that I need an "exit" button on the form that will allow the user to escape from the loop/userform and do other work if needed. When I click on any of my buttons that close or hide the userform, it returns control to the loop, and just opens the userform back up with the next inconsistent record. Now I've added an "exit" button, but I need to do more than just hide or close the userform, I need to break the loop. The loop I need to break is a for..next loop, but it is not the "closest" for..next loop- what is the best way to exit that loop? Module 1 'contains the code to pull records, ID inconsistent data, loop through that data, and call the userform Private Sub 1 declarations For... ' <---This is the loop that I need to exit If... If... For... 'another For..next loop If... Userform1.show end if exit for end if end if Exit for 'more stuff Exit Sub Many thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping procedure calls userform; how to exit loop (via userform button)?
Set a public property called say Cancel in the userform module/ Have your
exit button set it to True, else set it to False. Also, when you exit, hide the form, not unload. Then Private Sub 1 declarations For... ' <---This is the loop that I need to exit If... If... For... 'another For..next loop If... Userform1.show If Userfom1.Cancel Then Exit For end if exit for If Userfom1.Cancel Then Exit For end if end if Exit for 'more stuff Exit Sub -- HTH Bob Phillips "KR" wrote in message ... Hi all- using Win2K, XL2003 I have a userform that pulls inconsistent records and allows the user match certain items to remove the inconsistent data. I've written it so it loops through each item in the workbook and presents them all to the user. Now that I'm testing it to see how it works, I realize that I need an "exit" button on the form that will allow the user to escape from the loop/userform and do other work if needed. When I click on any of my buttons that close or hide the userform, it returns control to the loop, and just opens the userform back up with the next inconsistent record. Now I've added an "exit" button, but I need to do more than just hide or close the userform, I need to break the loop. The loop I need to break is a for..next loop, but it is not the "closest" for..next loop- what is the best way to exit that loop? Module 1 'contains the code to pull records, ID inconsistent data, loop through that data, and call the userform Private Sub 1 declarations For... ' <---This is the loop that I need to exit If... If... For... 'another For..next loop If... Userform1.show end if exit for end if end if Exit for 'more stuff Exit Sub Many thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping procedure calls userform; how to exit loop (via userform button)?
Change your 'Exit For' s for 'Next'
after UserForm1.Show, insert code for : If CloseButtonPressed then Exit For or If CloseButtonPressed Then Goto YourLabel and put a Label in the code beyond the appropriate 'Next' -- HTH Roger Shaftesbury (UK) "KR" wrote in message ... Hi all- using Win2K, XL2003 I have a userform that pulls inconsistent records and allows the user match certain items to remove the inconsistent data. I've written it so it loops through each item in the workbook and presents them all to the user. Now that I'm testing it to see how it works, I realize that I need an "exit" button on the form that will allow the user to escape from the loop/userform and do other work if needed. When I click on any of my buttons that close or hide the userform, it returns control to the loop, and just opens the userform back up with the next inconsistent record. Now I've added an "exit" button, but I need to do more than just hide or close the userform, I need to break the loop. The loop I need to break is a for..next loop, but it is not the "closest" for..next loop- what is the best way to exit that loop? Module 1 'contains the code to pull records, ID inconsistent data, loop through that data, and call the userform Private Sub 1 declarations For... ' <---This is the loop that I need to exit If... If... For... 'another For..next loop If... Userform1.show end if exit for end if end if Exit for 'more stuff Exit Sub Many thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping procedure calls userform; how to exit loop (via userform button)?
Public bContinue as Boolean
bContinue = True For... ' <---This is the loop that I need to exit If... If... For... 'another For..next loop If... Userform1.show ' in the exit button of the userform, set bContinue = False end if if Not bcontinue then exit for Next end if end if if Not bcontinue then Exit for Next 'more stuff Exit Sub -- Regards, Tom boxily "KR" wrote in message ... Hi all- using Win2K, XL2003 I have a userform that pulls inconsistent records and allows the user match certain items to remove the inconsistent data. I've written it so it loops through each item in the workbook and presents them all to the user. Now that I'm testing it to see how it works, I realize that I need an "exit" button on the form that will allow the user to escape from the loop/userform and do other work if needed. When I click on any of my buttons that close or hide the userform, it returns control to the loop, and just opens the userform back up with the next inconsistent record. Now I've added an "exit" button, but I need to do more than just hide or close the userform, I need to break the loop. The loop I need to break is a for..next loop, but it is not the "closest" for..next loop- what is the best way to exit that loop? Module 1 'contains the code to pull records, ID inconsistent data, loop through that data, and call the userform Private Sub 1 declarations For... ' <---This is the loop that I need to exit If... If... For... 'another For..next loop If... Userform1.show end if exit for end if end if Exit for 'more stuff Exit Sub Many thanks, Keith -- The enclosed questions or comments are entirely mine and don't represent the thoughts, views, or policy of my employer. Any errors or omissions are my own. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping procedure calls userform; how to exit loop (via userform button)?
I will disagree with the suggestions you've received so far. Adding a
boolean and using an Exit For is a nothing but a disguise for a GOTO. So, you might as well do just that. Or, design the entire procedure to be more user-driven. Using Tom's architecture of bContinue, use: '... '... Userform1.show if not bContinue then goto UserDone '<<<<< '... '... UserDone: 'More stuff End Sub A more user-driven and modular design would be to 'package' your business decisions of what is an inconsistent record and how you process it into specific subroutines/functions. Then, show the userform and as long as the user in interested, use the modules to retrieve the next inconsistent record and/or process one. When the user clicks done, you are done. Something along the lines of: The userform would have three buttons: Update, Next, Exit. Update updates the record based on what has been entered in the userform and displays the next inconsistent record. Next simply displays the next record, and Exit stops processing. The code for the three would look like: Update_click: SaveUpdatedData appropriate arguments GetNextRecord id-of-current-record, appropriate arguments Next_click: GetNextRecord id-of-current-record, appropriate arguments Exit_click: Me.Hide (or Unload Me depending on what is more appropriate) userform_activate: GetNextRecord 0 In the Standard module: sub SaveUpdateData (byval appropriate arguments) '... end sub sub GetNextRecord (id-of-current-Record, byRef appropriate arguments) '... end sub sub getGoing userform1.show end sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi all- using Win2K, XL2003 I have a userform that pulls inconsistent records and allows the user match certain items to remove the inconsistent data. I've written it so it loops through each item in the workbook and presents them all to the user. Now that I'm testing it to see how it works, I realize that I need an "exit" button on the form that will allow the user to escape from the loop/userform and do other work if needed. When I click on any of my buttons that close or hide the userform, it returns control to the loop, and just opens the userform back up with the next inconsistent record. Now I've added an "exit" button, but I need to do more than just hide or close the userform, I need to break the loop. The loop I need to break is a for..next loop, but it is not the "closest" for..next loop- what is the best way to exit that loop? Module 1 'contains the code to pull records, ID inconsistent data, loop through that data, and call the userform Private Sub 1 declarations For... ' <---This is the loop that I need to exit If... If... For... 'another For..next loop If... Userform1.show end if exit for end if end if Exit for 'more stuff Exit Sub Many thanks, Keith |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping procedure calls userform; how to exit loop (via userform button)?
"Tushar Mehta" wrote in message om... I will disagree with the suggestions you've received so far. Adding a boolean and using an Exit For is a nothing but a disguise for a GOTO. So, you might as well do just that. By that perverse logic, an If with an Else could be construed as nothing more than a Goto. Wow! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping procedure calls userform; how to exit loop (via userform button)?
In article ,
says... "Tushar Mehta" wrote in message om... I will disagree with the suggestions you've received so far. Adding a boolean and using an Exit For is a nothing but a disguise for a GOTO. So, you might as well do just that. By that perverse logic, an If with an Else could be construed as nothing more than a Goto. Wow! {shrug} If you can't tell the difference... -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i make a button from a userform exit onto the document in . | New Users to Excel | |||
Disable Exit button on a UserForm | Excel Discussion (Misc queries) | |||
Userform Exit button | Excel Programming | |||
How to disable the Exit Button of UserForm in Excel? | Excel Programming | |||
Opening a UserForm causes a crash in the Exit procedure (Which is never called) | Excel Programming |