Dismissing Userforms

[Martin Beir]

Office X - Excel on New iMac with Intell processors.

Create 3 Userforms each with a single Command button.


Userform1 (launch first)
Code: Private Sub CommandButton1_Click()
Userform2.Show
End Sub

Userform2
Code: Private Sub CommandButton1_Click()
Unload Me
Userform3.Show
End Sub

Userform3
Code: Private Sub CommandButton1_Click()
Unload Me
Unload Userform1
End Sub


If when Userform 3 is displayed I click the command button and then step
through the code with key F8 all is fine.

However, if I let the code run in the normal manner then the Userforms
disappear from the screen but Excel locks itself into Limbo mode. I cannot
edit code or quit Excel. I have to Force Quit Excel.

I have tried this on a minimac and suffer the same problem.

Any ideas?

Regards,

Martin Beir

 

[JE McGimpsey]

Office X - Excel on New iMac with Intell processors.

However, if I let the code run in the normal manner then the Userforms
disappear from the screen but Excel locks itself into Limbo mode. I cannot
edit code or quit Excel. I have to Force Quit Excel.

Any ideas?

First, Userforms in XLv.X are somewhat hosed in their implementation -
XL04 is a bit better, but I don't think the version is the culprit here.

When you dismiss Userform3, VBA tries to pass control back to Userform2,
but that's been unloaded already, leaving you in limbo.

Also, I recommend defining object variables for classes rather than
generating temp variables on the fly (mostly personal preference, but it
allows for the rare situation where I have multiple instances of a
userform), so I'd do it like this (tested in XL04 only):

Here's one way:

Regular code module:

Public fmForm1 As UserForm1
Public fmForm2 As UserForm2
Public fmForm3 As UserForm3

Public Sub StartThingsOff()
Set fmForm1 = New UserForm1
Set fmForm2 = New UserForm2
Set fmForm3 = New UserForm3

fmForm1.Show

Unload fmForm3
Unload fmForm2
Unload fmForm1
End Sub

Userform1 class module:

Private Sub CommandButton1_Click()
Me.Hide
If Not fmForm2 Is Nothing Then fmForm2.Show
End Sub


Userform2 class module:

Private Sub CommandButton1_Click()
Me.Hide
If Not fmForm3 Is Nothing Then fmForm3.Show
End Sub

Userform3 class module:

Private Sub CommandButton1_Click()
Me.Hide
End Sub

 

[Martin Beir]

First, Userforms in XLv.X are somewhat hosed in their implementation -
XL04 is a bit better, but I don't think the version is the culprit here.

When you dismiss Userform3, VBA tries to pass control back to Userform2,
but that's been unloaded already, leaving you in limbo.

Also, I recommend defining object variables for classes rather than
generating temp variables on the fly (mostly personal preference, but it
allows for the rare situation where I have multiple instances of a
userform), so I'd do it like this (tested in XL04 only):

Here's one way:

Regular code module:

Public fmForm1 As UserForm1
Public fmForm2 As UserForm2
Public fmForm3 As UserForm3

Public Sub StartThingsOff()
Set fmForm1 = New UserForm1
Set fmForm2 = New UserForm2
Set fmForm3 = New UserForm3

fmForm1.Show

Unload fmForm3
Unload fmForm2
Unload fmForm1
End Sub

Userform1 class module:

Private Sub CommandButton1_Click()
Me.Hide
If Not fmForm2 Is Nothing Then fmForm2.Show
End Sub


Userform2 class module:

Private Sub CommandButton1_Click()
Me.Hide
If Not fmForm3 Is Nothing Then fmForm3.Show
End Sub

Userform3 class module:

Private Sub CommandButton1_Click()
Me.Hide
End Sub

I do not fully understand the logic of this but it does work
perfectly Excel X. Thank you,