Unload/hide

[DaK]

I have a user form that is open, I click a button on the user form and another user form pops up into view. I press a cancel button on the second user for to close it. But the user form does not close, it simply hides behind the first form. I've tried "hide" and "unload" to close the user form but neither seems to work.

Any suggestions?

TIA DaK

[Mark O'Brien]

A couple of questions:

1. What version of Excel are you using?
2. Are you using modeless userforms?
3. Are you hiding/unloading the first userform when you show the second userform?
4. Are you using Application.ScreenUpdating = False?

[DaK]

1. Excel 2000 Small Business

2. Its whatever the default is, I can never remember if that is modal or modeless.

3. Well, I actually did not describe it correctly in my origional post. So here it is. From the first form, there is a button that opens another form. I do not unload/hide the first so it is still visible. From the second form, the user makes a choice which then loads a third form. The second form is then unloaded and the first form is still visible. Now, when the user is done with the third form, they press a "done / cancel" button and then the form is unloaded/hidden. This is the form I am having trouble with, it just hides behind the first form instead of being unloaded.

4. Yes

Thanks for replying...
DaK

[DaK]

It appears that no forms are hiding. I went back and made it so that the first for would hide when the second one was show but the first form just stays there. I tried both modal and modeless. And after I close the second form and drag the first one around the screen, it leaves a trail of its self on the screen until I close the form.

What is going on???

DaK

[Mark O'Brien]

Ok, the dragging over the screen thing. This is one of my favourite things. You can't (actually "shouldn't" is better)use application.screenupdating = false when you show a userform. That's why you're painting the form all over the place. Get rid of this and see if the other forms hide when you want them to. (which it wont, BTW, but it's worth the exercise)

The reason it wont work is because say for example you have userform1 and userform2, on userform1 you have a commandbutton with this code:

Private Sub CommandButton1_Click()

  UserForm2.Show

  Me.Hide

End Sub

When UserForm2 is shown, this code is still on the line "UserForm2.Show" until it is hidden or unloaded. So the "Me.Hide" to hide UserForm1 doesn't get executed until UserForm2 is removed. (this is probably what you're getting)

Anyway, the simple fix is to hide the form first, then show the other form, which can be done in two ways:

Me.Hide

UserForm2.Show

This just hides the form, it still exists and takes up memory. This other (preferred) method unloads the form and removes it from memory:

Unload Me

UserForm2.Show

Both of these examples, to me, are counter intuitive, but there you go.

HTH

[DaK]

Thanks for the help, I appreciate it.

DaK