VBA – User Forms Open/Close

I have 2 User Forms.
The 1st one is loading (.show) when I push a command button in document.
The 2nd User Form is loading when I push a command button placed in the 1st User Form.
I have command buttons to close them (.hide).
The problem is when I close the 2nd User Form from “x” right-up window (form) button and then I open again the UserForm2 from UserForm1.
The logic is blocked and has to be debugged.
Ex. the attached file.
Any suggestions?
UserForms.doc
LVL 21
viki2000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TommySzalapskiCommented:
Take the Hide lines out of the Terminate functions. It does that automatically.
0
TommySzalapskiCommented:
Also, you are using Modal dialog boxes so there's no need to Hide and Show Userform1 from Userform2. I would use the following code.

Userform1
Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
UserForm2.Show
End Sub

Open in new window


Userform2
Private Sub CommandButton1_Click()
UserForm2.Hide
End Sub


Private Sub UserForm_Initialize()
test = True
End Sub

Open in new window

0
viki2000Author Commented:
OK, but I want only one UserForm opened at a certain time.
With the code above, the both UserForms are opened when the 2nd one is opened.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

viki2000Author Commented:
And when you say Modal Dialog boxes, do you refer to those simple message boxes with YES, NO, Cancel? as here:
http://gregmaxey.mvps.org/Custom_MsgBox.htm

If you refer to them then are not good for me.
The example above in doc file is just a simple example. My real application is with several complex UserForms, hundreds of controls...
0
TommySzalapskiCommented:
No, by modal I mean they take focus and don't give it back until the form is dismissed.

Take your original code and just remove both terminate functions. That works too. At least take the hide lines out of those two functions. It does that automatically.
0
viki2000Author Commented:
I tried is not working.
The idea is when I close the UserForm2 then I want to return back to UserForm1 even when I close UserForm2 by “x” right-up window
0
Rory ArchibaldCommented:
The second bit of code in Userform1 should be:

Private Sub CommandButton2_Click()
Dim frm As Userform2
Me.Hide
set frm = new userform2
frm.Show
set frm = nothing
Me.Show
End Sub

Open in new window


as mentioned, there is no need to reshow form1 from form2 - form1's code will take care of it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
viki2000Author Commented:
When I close from "x" corner is working but when I repeat several times also using commandbutton1 from UserForm2 I get errors
0
Rory ArchibaldCommented:
Your userform2 button code should just be:

Unload me

Open in new window

0
viki2000Author Commented:
Now is good.
Thank you.
0
viki2000Author Commented:
The solution is nice and good, but now for my knowledge I need an explanation so I can learn.

Private Sub CommandButton2_Click()
Dim frm As UserForm2
Me.Hide
Set frm = New UserForm2
frm.Show
Set frm = Nothing
Me.Show
End Sub

When I push the CommandButton2 in the code above how come the logic, the code stop at “Set frm = Nothing” until UserForm2 is closed and only after that shows UserForm1 with “Me.Show”?
I would expected that once that I push CommandButton2 everything in the subroutine is executed, including “Me.Show” (equivalent with UserForm1.Show) and therefore to have both user forms on screen.
So, why stops and waits there?
0
Rory ArchibaldCommented:
It actually stops at frm.Show because your form is modal, which means that everything else stops while it is displayed. Once it is hidden or unloaded, processing resumes at the next line of code as normal.
0
viki2000Author Commented:
Thanks.
0
viki2000Author Commented:
Then a simpler code that is working is:

For UserForm1:
Private Sub CommandButton1_Click()
UserForm1.hide
End Sub

Private Sub CommandButton2_Click()
UserForm1.hide
UserForm2.Show
UserForm1.Show
End Sub

For UserForm2:
Private Sub CommandButton1_Click()
UserForm2.hide
End Sub


Why should I use the Dim and Set and "longer" code?
0
Rory ArchibaldCommented:
Depends if you want to do it properly or not. Your code will work most of the time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.