How do you unload only the top UserForm?

[skysurfer]

Hi,

When I have two UserForms open at the same time and I click on the "X" close button in the upper right hand corner of the top most form, ALL the forms unload. Is there code that changes that X button to only unload the form in which it resides and keep the other form loaded/shown?

Thanks,

Lawrence

[Leith Ross]

Hello skysurfer,

You should post your workbook. I have tried to reproduce this problem without success.

[skysurfer]

Hello skysurfer,

You should post your workbook. I have tried to reproduce this problem without success.

Hi Leith,

Just got back and was preparing the workbook to be posted for you when I tested the issue...and there was no issue. That is, after shutting down Excel and then reloading the program and the workbook there is now no problem.

However, I still would like to gain greater control over that X close button. Is it possible to apply code to it that will make the form hide instead of unload?

Thanks!

Lawrence

[Leith Ross]

Hello skysurfer,

The UserForm can tell if the close box was clicked or not.

Please Login or Register  to view this content.

[skysurfer]

Thanks Leith.

Where do I put that code? I tried it in the Forms and clicking on the X still unloads the UserForm. I'm trying to hide the form so when it is triggered to show again the last page on the MultiPage will be the 1st page seen.

Lawrence

[Leith Ross]

Hello skysurfer,

1. Open the Visual Basic Editor and select a UserForm
2. Press F7. This inserts the default event Private Sub UserForm_Click() into the UserForm.
3. Above this window are 2 drop down lists. The one on the left is for Objects and the one on the right is for events.
4. Click the righthand drop down list. Scroll down the list to QueryClose
5. Insert the following line of code If CloseMode Then Me.Hide
6. Repeat these procedures for each UserForm you want to hide instead of close.

[skysurfer]

Leith,

I did that and the form hides when the X is clicked...I think.

You see, there are 3 pages on the UserForm and I'm trying to get the form to re-open on the last page viewed. It does not behave that way with this code. Am I asking for the wrong solution? Is this even possible?

Lawrence

[Leith Ross]

Hello skysurfer,

Like the Captain said in Coolhand Luke "What we got here is... failure to communicate." I think we are talking about 2 different things. If you post your workbook. it will put us both on the same page and I can answer your question.

[skysurfer]

Leith,

I've attached a workbook with a simple MultiPage UserForm.

Normally, when you unload a UserForm and then relaunch it, the page shown is the default page (usually page 1). Igt will always reopen to that same page.

However, if instead you have a Close button that hides the form like in the attachment, relaunching the form will simply restore the form as it was when you last viewed it. That means if you were viewing page 2 when you hid the form, page 2 will be the same page you see when you show the form again.

What I want to do is change the functionality of the X button on the top right corner of the form so that it has that same functionality: clicking it does not unload the form, but hides it.

I hope that wasn't clear as mud. (BTW, Cool Hand Luke rocks!)

Lawrence

[Leith Ross]

Hello skysurfer,

Coolhand Luke has always been one of my favorites. The MuliPage object remembers the last Page you selected. To force it to a particular page when the UserForm is displayed, add this to the UserForm. The MultiPage Pages are zero based. The first page is 0, the second is 1, etc.

Please Login or Register  to view this content.

[skysurfer]

Leith,

What I'm after is a temporary bookmark. In my workbook, load the UserForm and select Page 2. Then, hit the "Close" button. when you click the "Show Userform" button again now you should be looking at Page 2. That's because the UserForm did not unload but was hidden.

I want to apply that same functionality to the X button. Because if you hit THAT button, the UserForm unloads and forgets what page you were just looking at.

So, I need the code that tells X NOT to unload the form, but to hide it.

Lawrence

[Leith Ross]

Hello skysurfer,

This code will accomplish what you are after...

Please Login or Register  to view this content.

[skysurfer]

SUCCESS!!!

Thank you!

_______________
Dragline: "Hell, he's a natural-born world-shaker."

[Leith Ross]

Hello skysurfer,

Glad we got it working. I like your signature line.

Luke "Well, I don't care if it rains or freezes, long as I got my plastic Jesus, sittin' on the dashboard of my car.
Comes in colors, pink and pleasant, glows in the dark cause it's irridescent."