Help, One form Closes another Form when closing itself...

[MikeZz]

I have a form that loads when a workbook is open - call it 1st Form.
1st Form compares the current user to a list in the file.
If they are not on the list, it runs the 1st line of code below which calls
up another frmNotFound (aka 2nd Form) with some messages and buttons.

1st Form ShowModal = false, 2nd Form ShowModal = true

The 2nd userform has a "Continue" button with the second line of code
attached below. Basically, I want it to close this 2nd form and continue
loading the 1st form.

Upon returning to 1st form, the rest of the code is executed but at the very
end, it seems to close itself and I'm not sure why.

Code in the 1st form which calls the 2nd form:
-----------------------------------------------------
frmNotFound.Show

Code in the 2nd form which closes itself... but needs to return to the 1st
form:
-----------------------------------------------------
Private Sub cmdNon_Click()
Unload frmNotFound
End Sub

Thanks,
MikeZz

 

[Tom Ogilvy]

try making the second form
ShowModal = False

 

[Tom Ogilvy]

also

frmNotFound.Show


probably would be better with

me.Hide
frmNotFound.Show
me.show

 

[JRForm]

MikeZz,

Try this it works for me.

Create a code module if you do not already have one then paste in this code-

'Declare variables
Public intList As Boolean
Public bolDone As Boolean


Function RuListed(intList As Integer)

If intList = 0 Then
Unload UserForm2
Else
bolDone = True
UserForm2.Show
End If

End Function

Then in the UserForm1 put this in


Private Sub UserForm_Activate()
If bolDone = True Then Exit Sub
'put in your code to check for the user here
MsgBox "You are not the user I need! " 'DELETE THIS LINE
RuListed (1)
End Sub


Private Sub UserForm_Initialize()
'bolDone is used to capture one cycle of form1 to form2
bolDone = False
End Sub

Then place this code behind the cmdContiue button

Private Sub cmdcontinue_Click()
RuListed (0)
End Sub

 

[MikeZz]

I think this file must have some major corruption.

I saved everything, closed and reopened a few times, didn't open the VBA
window and then it seemed to work ok.

I DO HAVE Another question though...
If i "2ndForm.hide", when does it actually close?
When 1stForm is closed? when the file is closed? when excel is closed?

The reason I ask is because if 1stForm is closed then reopened, does it open
a new 2ndForm or the old one? Don't want to have a bunch of hidden forms
taking up resources.

Thanks again!

 

[Tom Ogilvy]

For some reason, I can't post back to your last post, so:

In my scenario,

Userform1 is shown

Userform1 executes this code

me.Hide ' userform1 is hidden
Userform2.show 'Useform2 is shown and code waits until userform2
' is hidden or unloaded
me.show ' Userform 2 has been hidden or unloaded, reshow myself


Code in Userform2 unloads userform2.

 

[paul cain]

For the sake of other users with 1 form closing the parent form.... I have had this same problem with vba Excel, my program is driven entirely from my MainForm which is modeless but subsequent forms opened from it were modal. I have had an intermittent problem with MainForm closing when the modal form closes. MainForm closes (or disappears, I am not really sure what has happened to it) without even triggering the QueryClose event. By intermittent, I mean some code has the problem, some doesn't, and I do not know what the significant difference is between the codes except perhaps that the code that does crash is usually under development still, not fully debugged. I have only found 1 fix as mentioned in this discussion, and that is to make all forms modeless. It is not possible to simply interchange them from modal to modeless on the run. However, it is possible to add MainForm.Enabled = False when I open the secondary forms to prevent the user changing the data in MainForm, then reset it True after the second form is unloaded. In practice, I had to reset it within a QueryClose sub in case the user presses X box to close.

 

[GS]

It happens that paul cain formulated :

For the sake of other users with 1 form closing the parent form.... I have
had this same problem with vba Excel, my program is driven entirely from my
MainForm which is modeless but subsequent forms opened from it were modal. I
have had an intermittent problem with MainForm closing when the modal form
closes. MainForm closes (or disappears, I am not really sure what has
happened to it) without even triggering the QueryClose event. By
intermittent, I mean some code has the problem, some doesn't, and I do not
know what the significant difference is between the codes except perhaps that
the code that does crash is usually under development still, not fully
debugged. I have only found 1 fix as mentioned in this discussion, and that
is to make all forms modeless. It is not possible to simply interchange them
from modal to modeless on the run. However, it is possible to add
MainForm.Enabled = False when I open the secondary forms to prevent the user
changing the data in MainForm, then reset it True after the second form is
unloaded. In practice, I had to reset it within a QueryClose sub in case the
user presses X box to close.

I'm inclined to agree with Tom Ogilvy's suggestion to hide userform1
while userform2 is in use, then unhide userform1 when code resumes.
That concept works for me!

What you might want to pay attention to is whether ScreenUpdating is
turned off anywhere, AND/OR whether a DoEvents function is needed.