How do you close a window with a hidden UserForm in it?

**skysurfer** · 04-14-2010, 04:14 PM

Hi,

Is there a work-around for safely closing an Excel window when there is a hidden UserForm in it?

I have a multipage UserForm with a "close" command button that hides the form. And if while that form is hidden the user clicks the Excel "Close Window" button (the small X on the toolbar under the big X that closes the entire program), Excel crashes. I guess Excel does not like to shut down an active window with a hidden UserForm in it.

Is there a way around this...other than simply not using the "hide" in VBA?

Lawrence

**Leith Ross** · 04-14-2010, 05:00 PM

Hello Lawrence,

Instead of hiding the UserForm, I have code to make the UserForm behave like a Window with minimize, maximize, and a close buttons. This will allow you to either close it completely when you are done using it or minimize it to get it out of the way. Let me know if you are interested.

**skysurfer** · 04-14-2010, 05:22 PM

Leith,

That code sounds very cool. What happens if the user closes the Excel window (the little X) when one of your forms are minimized or maximized?

Lawrence

**Leith Ross** · 04-14-2010, 05:47 PM

Hello Lawrence,

The UserForm will remain until the user closes it.

**skysurfer** · 04-14-2010, 05:56 PM

Leith,

Could I try out your code. It sounds like it really takes the UserForm to the next level!

Lawrence

**Leith Ross** · 04-14-2010, 06:49 PM

Hello Lawrence,

It is important that you set the UserForm ShowModal property to False for this to work correctly. Copy the macro code below into its own VBA module.
Macro to Add Window Buttons to a UserForm

Please Login or Register  to view this content.

UserForm Code

Please Login or Register  to view this content.

**skysurfer** · 04-14-2010, 07:10 PM

Leith,

This looks awesome. If I use it I will definitely preserve the header.

Dumb questions:

1. Is Modeless the same as setting the modal property to false? The Userform is currently coded to launch modeless as it is meant to be moved around and out of the way of charts.

2. Will I still be able to initialize the UserForm? I have a lot of code in the initialization procedure.

Thanks!

Lawrence