UserForm locking up

[excelnut1954]

When UF4 comes up, the user enters the PO# in TextBox1. If this PO# is
already on the list, a message comes up letting the user know.

It is suppose to then unload the UF, then reload it. I wanted the UF
unloaded instead of just clearing TextBox1 in case the user entered
data in some of the other textboxes before getting to TextBox1.

The problem is that after the message comes up, the program locks up,
and you have to take Excel down via the task manager.

This works fine if I only have TextBox1 cleared after the message. But,
that's not how I want it to run. Can anyone see a problem with the
coding?
Thanks,
J.O.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'This will check to make sure the PO# the user entered is not already
on
'the list. If so, message box comes up.

With Worksheets("Official list")
If TextBox1.Text <> "" And Not .Range("j:j").Find(TextBox1.Text,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "This PO# is already on the list. Please enter the
information in the existing Record.later."

Unload UserForm4
UserForm4.Show
' TextBox1.Text = Clear
' Cancel = True

End If
End With

End Sub

 

[Bob Phillips]

Why don't you clear all of the input fields rather than unload and reload
the form?

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Guest]

Userform4 can't unload because the event is still active. When you issue the
Show command, I can't say exactly what happens. It may be waiting for the
event to terminate (which it can't)

I would assume some other code has shown userform4 initially. The easiest
is to design that code to look at the form and show it again if a public
variable is set.

So in a general module:

Public bShowAgain as Boolean

Sub MasterSub()

do
Userform4.Show
loop while bShowAgain

End Sub

then your code would be:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'This will check to make sure the PO# the user entered is not already
on
'the list. If so, message box comes up.

With Worksheets("Official list")
If TextBox1.Text <> "" And Not .Range("j:j").Find(TextBox1.Text,
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then
MsgBox "This PO# is already on the list. Please enter the
information in the existing Record.later."

bShowAgain = True
Unload UserForm4
End If
End With

End Sub

Private Sub Cmd_OK_Click()
bShowAgain = False
Unload Me
End Sub

and reset bShowAgain in other events as appropriate.

 

[excelnut1954]

Thanks for the responses guys. I may just use Bob's suggestion for the
quick fix for now. But I'll look at Tom's reply later, break it down,
and maybe use that idea. More things to take into consideration, I
guess. I didn't realize there would be a problem unloading a userform
at any point.
Thanks again for the quick responses.
J.O.

 

[Bob Phillips]

When you unload a userform, that should be it. Any code that you try to run
after that unload should be in the procedure that showed the form initially,
not in the userform itself.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)