Why, after launching a userform from a userform, does unloading the 2nd userform close both?

1
votes

A button on a worksheet launches a macro that opens a userform (say, userform1). Userform1 is loaded non-modal in order for the user to use both userform1 and the worksheet (i.e., click cells) for input. There is a button on userform1 that, when clicked, opens another userform (say, userform2). Userform2 is modal. Clicking a Cancel button on userform2 unloads userform2 as it is supposed to; however, it, for some reason, also unloads userform1, which I do not want. If I make userform1 modal, then unloading userform2 does not unload userform1; however, the user can no longer use (i.e., click) the cells in the worksheet. I cannot find any info that will give me a clue as to why unloading one userform unloads both.

2
excelvbamodal-dialoguserform
How exactly is Form 2 being closed? What command / syntax? Are you using 'Unload.Me'? – Wayne G. Dunn
Ahhh, I recreated the problem, did some research, found a suggestion at tech-archive.net/Archive/Excel/… added 'DoEvents' before and after the 'Unload Form2' AND before and after the 'Form2.Show'!! I'm pretty certain you don't need ALL those DoEvents, but you give it a try... – Wayne G. Dunn
I was reading thru the site you suggested and the submitter said he closed and restarted his excel app and that fixed the problem. I did that and now everything works as it should. Which would seem to suggest that using the DoEvents (as you suggested) would likely have worked. Thanks for the help (though I wish I had a better understanding of what was really happening). – BillD
Intersting test I just did... I commented out the DoEvents, closed, then reopened the Excel file, and the undesired result returned - it closed BOTH forms again. I added the 'DoEvents' only to the line following 'Form2.show' and the problem went away. Hope this helps.. – Wayne G. Dunn
I just did the same test and the undesired result returned for me as well. I put the 'DoEvents' where you suggested and it does work. I'm still not sure why that works but...don't argue with success. Thanks for the help – BillD

2 Answers

0
votes

UserForm2.Show

a = 1 ' only 1 more line of any code to execute

this will do the trick. At least this worked for me with the same issue...

0
votes

I am very happy I just stumbled upon this old thread.

What I discoverd is that the problem goes away when the VBA-editor window is closed. You really have to CLOSE it, minimizing the window is not enough. It also does not matter if the window is opend on the same screen or not. Only closing it did the trick for me. What I discoverd is that as soon as Form2 was unloaded, the VBA-editor shows Form2, no matter what other code-module I was just in.

I aimed to isolate the problem in a TestWorkbook.xlsm(review code below). I tried out the suggestions DoEvents and a line of code after Form2.Show and both helped. In my DevelopmentAddIn.xlam they did not help, the 1st form still closes. So the problem could still lay in the more complex code of my AddIn.

But like I said, closing the VBA-editor window does the trick, all though I still do not understand why.

TestWorkbook.xslm (two userforms Form1 and Form2 and a code module mLoad)

mLoad:

Option Explicit
Public Changed As Integer

'***Load the 1st form
Public Sub LoadFirstForm()
    Load Form1

    'allow user to change the active workbook
    Form1.Show vbModeless
End Sub

'***Load a 2nd form (from Form1)
Public Sub LoadSecondForm()
    Dim a  As Integer

    Load Form2

    'continue after 2nd form closes
    Form2.Show vbModal

    'suggestions
    a = 1
    DoEvents

    '2nd form was changed
    If Changed = 1 Then
        Form1.InfoBox.Value = "Changed"

        'process changes

    '2nd form is unchanged
    Else
        Form1.InfoBox.Value = "Unchanged"
    End If
End Sub

Form1 (with button cmdLoad and textbox InfoBox)

'***Load the 2nd form (from Form1)
Private Sub cmdLoad_Click()
    LoadSecondForm
End Sub

Form2: (with button cmdOK)

Option Explicit

'***Initial status is 'unchanged'
Private Sub UserForm_Initialize()
    Changed = 0
End Sub

'***Status is 'changed'
Private Sub cmdOk_Click()
    Changed = 1

    'close 2nd form and continue
    Unload Me
End Sub