Kill a UserForm

[Greg]

I am trying figure out how to "terminate" a userform and code in
progress if a user clicks a "Cancel" command button or the "X" on the
form. Here is the problem. The user enters text in a textbox that
could result in an error. That text is passed as a variable to another
sub as part of a Do Loop. If the error is generated, the user is
notified with a msgbox and the form is displayed with the invalid entry
highlighted. At this point, I want the user to be able to cancel the
process and eliminate any additional UserFrom code from running. I
mean I want to kill it until it stops breathing completely.

I can't get it worked out. I have duplicated the problem in the simple
scenario provided below. All it takes is a user form, 1 text box, and
2 command buttons. Enter the value "2" in the text box and click the
command button 1. When the error is generated and you are returned to
the form then click the cancel button. This is where I want to strike
the death blow, but as you will see the code continues to running
(bypassing oddly enough the call) until the count of 10 is reached.

All help welcome. Thanks.

Option Explicit
Private Sub CommandButton1_Click()
Me.Hide
Dim i&
For i = 1 To 10
CallMsg i
If i > 2 Then MsgBox "Won't stop"
Next i
Exit Sub
End Sub

Sub CallMsg(ByVal i&)
MsgBox i
If i = Me.TextBox1.Text Then
On Error GoTo Handler
Err.Raise 5625
End If
Exit Sub
Handler:
MsgBox ("You must change the test in text box 1 to a number > 10")
With Me.TextBox1
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.Show
Err.Clear
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

 

[Jean-Guy Marcil]

Greg was telling us:
Greg nous racontait que :

I am trying figure out how to "terminate" a userform and code in
progress if a user clicks a "Cancel" command button or the "X" on the
form. Here is the problem. The user enters text in a textbox that
could result in an error. That text is passed as a variable to
another sub as part of a Do Loop. If the error is generated, the
user is notified with a msgbox and the form is displayed with the
invalid entry highlighted. At this point, I want the user to be able
to cancel the process and eliminate any additional UserFrom code from
running. I mean I want to kill it until it stops breathing
completely.

I can't get it worked out. I have duplicated the problem in the
simple scenario provided below. All it takes is a user form, 1 text
box, and 2 command buttons. Enter the value "2" in the text box and
click the command button 1. When the error is generated and you are
returned to the form then click the cancel button. This is where I
want to strike the death blow, but as you will see the code continues
to running (bypassing oddly enough the call) until the count of 10 is
reached.

I think that ideally this type of manipulation should be done outside of the
userform module, from a standard module with a sub to call the userform.

The problem I see is that the Me.Show in the called sub (CallMsg) causes the
compiler to give control back to the userfom and suspends the execution of
the rest of the code in both the called and the calling sub, but it
remembers where it stopped executing and continues the first chance it gets.

One way around that is to use a Function instead of a Sub:

'_______________________________________
Option Explicit
'_______________________________________
Private Sub CommandButton1_Click()
Me.Hide
Dim i&
For i = 1 To 10
If Not CallMsg(i) Then
Me.Show
Exit Sub
End If
Next i
Exit Sub
End Sub
'_______________________________________

'_______________________________________
Function CallMsg(ByVal i&) As Boolean
CallMsg = True
MsgBox i
If i = Me.TextBox1.Text Then
On Error GoTo Handler
Err.Raise 5625
End If
Exit Function
Handler:
MsgBox ("You must change the test in text box 1 to a number > 10")
With Me.TextBox1
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
CallMsg = False
Err.Clear
End Function
'_______________________________________

'_______________________________________
Private Sub CommandButton2_Click()
Unload Me
End Sub
'_______________________________________


As to CallMsg i being bypassed, I think it has to do with the fact that you
unloaded the form, so its subs are not available anymore, but the code
continues to run because the object is still active.. It is a type of "magic
form".

Try this as an example with a second sub being bypassed:

'_______________________________________
Option Explicit
'_______________________________________
Private Sub CommandButton1_Click()
Me.Hide
Dim i&
For i = 1 To 10
CallMsg i
If i > 2 Then
MsgBox "Won't stop"
CheckAfterUnloading
End If
Next i
Exit Sub
End Sub
'_______________________________________

'_______________________________________
Sub CallMsg(ByVal i&)
MsgBox i
If i = Me.TextBox1.Text Then
On Error GoTo Handler
Err.Raise 5625
End If
Exit Sub
Handler:
MsgBox ("You must change the test in text box 1 to a number > 10")
With Me.TextBox1
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
Me.Show
Err.Clear
End Sub
'_______________________________________

'_______________________________________
Private Sub CommandButton2_Click()
Unload Me
End Sub
'_______________________________________

'_______________________________________
Sub CheckAfterUnloading()
MsgBox Me.TextBox1.Text
End Sub
'_______________________________________

Finally, replace
'_______________________________________
If i > 2 Then
MsgBox "Won't stop"
CheckAfterUnloading
End If
'_______________________________________
by
'_______________________________________
If i > 2 Then
MsgBox "Won't stop"
MsgBox Me.TextBox1.Text
End If
'_______________________________________

and you will see that Me.TextBox1.Text stills works, even though the form
has been unloaded already. It seems that the final unloading has is waiting
for the code to finish executing and that the form object is still
available.

I am sure someone with a better understanding of these intricate matters
will be along shortly... but I thought I would share my findings!

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Greg]

JGM,

Thanks for the reply. I think I will be able to make the Function
method work for my purpose as regretfully most of my projects end up
being a house of cards and if I try something as massive as moving code
back to the module it is subject to collapse ;-)

I will try to remember this for next time.

 

[Word Heretic]

G'day "Greg" <[email protected]>,

Formalise your development methods.

Whenever you have a userform to create, also create a class to handle
the form. Ensure the form has a Set Property Parent so you can then
use the calling classes' methods to do any actual processing. Code in
a userform should be minimalised and be very form specific. Generally
speaking, a small bit of capture code for the control presses is all
that should be there, isolate all other processing as a common (and
public) function in the code module, or specific processing in the
handling class.

Steve Hudson - Word Heretic

steve from wordheretic.com (Email replies require payment)
Without prejudice


Greg reckoned:

 

[Greg Maxey]

Steve,

Thanks for the reply and sorry for the delay in acknowledging. I had a
family situation that called me away for a week.

I am relearning lessons learned and forgotten. Part of the problem as you
may know is that I only dabble in this stuff and when I learn something new
I may forget something learned previously.

I realize that I should minimize code in a userform, but while chasing
another goal I let the code grow out of hand ;-)

Would be nice if you could show a simple example of this process:

Whenever you have a userform to create, also create a class to handle
the form. Ensure the form has a Set Property Parent so you can then
use the calling classes' methods to do any actual processing.

Thanks.

 

[Word Heretic]

G'day "Greg Maxey" <[email protected]>,

See Dian Chapman's TechTrax website archive and files list as I have
provided a nice little example of a publicly accessibly progress bar
that does all this and more

Steve Hudson - Word Heretic

steve from wordheretic.com (Email replies require payment)
Without prejudice


Greg Maxey reckoned:

 

[Greg]

Steve,

I went there, couldn't fing your name listed. Searched the archive
for articles on "progress bar" and came up ziltch.

On top of that, I don't have a membership to TechTraz and I don't plan
to purchase one.

Thanks anyway.

Greg

 

[Word Heretic]

G'day "Greg" <[email protected]>,

Meant mousetrax sorry.


Steve Hudson - Word Heretic

steve from wordheretic.com (Email replies require payment)
Without prejudice


Greg reckoned: