VBModeless UserForm Closing/Crashing Excel

[DomesticedCrab]

Hi,

I have a Userform which opens as "vbModeless," it contains a series of buttons to open more Userforms that all open as "vbModeless." Very simple, but randomly some of the files will immediately close when selecting one of the buttons contained on the first opened form. If I remove the "vbModeless" the error goes away. Any thoughts?

Code to open first form:
Public Sub ControlsShow()


Controls.Show vbModeless


End Sub

Code to open second form (button hosted on first form):
Private Sub EnterPricing_Click()


PricingForm.Show vbModeless
Application.Calculation = xlAutomatic


End Sub

Nothing wild at all - but it will crash and crash. If I step through it, it functions fine and will no longer crash.

Could it be a form not unloading or something creating some rogue object??

Thank you

 

[Logit]

.
When you are done with the userform .. utilize this command :

Unload Me

You would place that connected to a command button.

 

[DomesticedCrab]

Thanks for the reply Logit.

In each of the forms I do utilize a "Close" button that does unload the forms.

If the user closes the form via the "X" button on each form - does that unload the form?

I am wondering if the forms are being closed via the "X", the form is not unloading right, and then when the user goes to reopened the form there is an issue as the previous cases of the form weren't unloaded and Excel closes down in response.

 

[Logit]

.
As far as I know, using the "X" to close a form is the same thing as the code "Unload Me"

 

[DomesticedCrab]

To follow-up -

I was able to resolve the problem. After testing about 10 or so solutions (close one form before other, don't open in vbmodeless, etc., etc., etc.) the one thing that works is to save the workbook before opening up the second form (activeworkbook.save). Why exactly this worked? I am not sure, but it resolved the problem in all the users I had testing.

 

[Logit]

.
So long as you found an answer. That is all that matters.

Cheers.

 

[Kaydron]

I recently ran into this issue I started the program with a modeless form. I then created and Showed a modal form from that main form inside a subroutine. When I left the subroutine the garbage collector destroyed the new form I created. I am guessing Excel feels if one userform gets destroyed all userform instances should get destroyed.

I solved this by keeping a reference to the created secondary/sub userform as a private class variable in my main userform, and I never unload the secondary/sub userform during my program I only hide it.