Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
KR KR is offline
external usenet poster
 
Posts: 121
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,120
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 32
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,071
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,120
Default 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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
how do i make a button from a userform exit onto the document in . Pieface New Users to Excel 1 April 17th 07 07:35 PM
Disable Exit button on a UserForm Noemi Excel Discussion (Misc queries) 1 September 14th 06 09:37 PM
Userform Exit button Luis Excel Programming 2 December 30th 04 04:50 PM
How to disable the Exit Button of UserForm in Excel? Alex Excel Programming 3 December 20th 04 12:55 AM
Opening a UserForm causes a crash in the Exit procedure (Which is never called) Brent McIntyre Excel Programming 0 May 12th 04 12:37 PM


All times are GMT +1. The time now is 10:25 AM.

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

About Us

"It's about Microsoft Excel"