# Solved: Excel freezing



## Jimmy the Hand (Jul 28, 2006)

Hi All,

I have some experience with Excel and VBA and subdued many a problems but this one knocked me out. The attachment is a small project of mine. It's nothing special, both the worksheets and the VBA code are irrelevant, and all is working fine, except one thing. The problem is with closing a userform contained within the workbook. If I close the userform with the red X in the upper right corner, all is OK. If I close it via VBA command, all _seems to be _OK, but Excel freezes as soon as I try to close the workbook.  I just don't understand this behavior.  Is there a difference between red X and Hide method of a userform? Can someone tell me what's wrong? Please? I can even imagine that it's only me, so if you find no problem, that also would be valuable information.

For your convenience, I created buttons to show and hide UserForm2. They have the most simple code of

```
UserForm2.Hide
```
and

```
UserForm2.Show
```
TIA

Jimmy

Ps: 
Please save and close any other works of yours before trying this, because your excel might freeze too.


----------



## bomb #21 (Jul 1, 2005)

Hi Jimmy. 

Code looks standard to me, and causes no probs.

Re: "I created buttons to show and hide UserForm2" is the hide button on the sheet or the form? (apparently a button on a sheet *can* fire while a form is running _if_ the form is "modal" or "modeless" -- one or the other, dunno which ; but only in "later" versions).

I've seen "Unload Me" used a fair bit over "UserForm.Hide" -- I have no idea what the difference is, but it may be (totally guessing) a "memory optimising" thing. Give a try, perhaps.

EDIT: see "How to remove a UserForm *from memory*" at http://support.microsoft.com/default.aspx?scid=kb;EN-US;829070 -- maybe.


----------



## Jimmy the Hand (Jul 28, 2006)

Thanks, bomb. 
"Unload Me" works fine. No freeze, no crash. I only regret the half day I spent on fuming about this...  

Oh, and  
I forgot to upload the workbook... looks like you can work even blindfolded :up:


----------



## bomb #21 (Jul 1, 2005)

Jimmy the Hand said:


> Thanks, bomb.
> "Unload Me" works fine. No freeze, no crash. I only regret the half day I spent on fuming about this...


Don't forget I'm completely guessing, might not be anything to do with that at all. Still, if it works ... :up:


----------



## Zack Barresse (Jul 25, 2004)

Hi guys, just wanted to clarify a few things here. There is a difference between Hide and Unload commands. First of all, the Hide is a method, whereas the Unload is a statement. Inherently they are two very different things and thus have two very distinct actions on the environment they are being used in.

The other issue is that the Hide method is like a cell format, it's for asthetic purposes only and doesn't serve a real purpose other than that. The Userform would still be loaded and held in memory, still there, just not visible. This is nice part about this is you can still hold any values issued to/from your userform while it's in memory. So if you are wanting to perform other things and wanting to use the userform either as an intermediary or afterwards, hiding might be the option you would choose.

Also note that you did not specify your Excel version. This is important because Excel 97 does not support modeless userforms. As Andy stated, you can't have a userform interact with the worksheet environment unless it's modeless, and version 2000 (9.0) or higher. This could be a reason for crashing. As Andy suggested, it may be other reasons as well, but it's certainly an option.



> Is there a difference between red X and Hide method of a userform?


To answer your question, yes, there is a difference. The red X in the upper right actually triggers the event QueryClose. So it's linked to an event, just as an Excel workbook is. The difference between the X button and the Hide method is that the X will actually close the userform (triggering two events, described in a second) whereas the Hide will not close it and keep all values/objects in memory.

There are a couple other events that are closely related as well. Those are the Deactivate and Terminate events of a userform. These may look the same to some people, but I assure you they are not. The Deactivate (which also has an Activate counterpart event) is pretty self-explanatory, it's used if the userform is deactivated for some reason, not closed. This particular event will not fire when the red X button is used.

The Terminate event will fire when a userform is closed. The difference is that the QueryClose event will fire before the Terminate event. It is important to know the order of events, especially if you are using multiple events on a userform.

Hope this helps.


----------

