My form doesn't close when I press the 'X' - SOLVED

[Darren Bartrup]

I have a form (frmSwitchboard) which opens another form (frmProcessReturns) when a command button is pressed (the first form is hidden rather than unloaded at this point).
When I close the second form and press the button to reopen it I get an run-time error message 400: 'Form already displayed; can't show modally'.
Sure enough, if I move the the first form out of the way the second form is still sitting there behind it. I can't activate it, but I presume this is because the first form is modal.

Here's the code that's relevant to the forms:

Option Explicit

Private Sub Workbook_Open()

    OpenForm

End Sub

In a module:

Option Explicit

Public Sub OpenForm()

    Dim strSQL As String
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    strSQL = "SELECT [tblTeamManagement].[fldTeamName], " & _
              "[tblTeamManagement].[fldIgnore], " & _
              "[tblTeamManagement].[fldToContact], " & _
              "[tblTeamManagement].[fldCCContact], " & _
              "[tblTeamManagement].[fldReContact] " & _
              "FROM [tblTeamManagement];"

    Load frmImportGAL
    frmImportGAL.Show
    
    InitialiseGlobalVariables
    Load frmSwitchboard                     <<<<< LOAD THE FIRST FORM.
    PopulateTeamNames frmSwitchboard.fraTeams, strSQL
    DisplayEmailText
    Application.Calculation = xlCalculationAutomatic
    frmSwitchboard.Show                    <<<<< SHOW THE FIRST FORM.
    
    Application.ScreenUpdating = True

End Sub

In frmSwitchboard form:

Private Sub cmdProcess_Click()

    Load frmProcessReturns              <<<<<< LOAD THE SECOND FORM.
    frmProcessReturns.Show             <<<<<< SHOW THE SECOND FORM.
    
End Sub

In frmProcessReturns form:

Private Sub UserForm_Initialize()

    Dim rsServiceType As New ADODB.Recordset
    
    frmSwitchboard.Hide       <<<<< HIDE THE FIRST FORM.

    With rsServiceType
        .Open "SELECT tblServiceType.fldServiceType " & _
              "FROM tblServiceType;", GV.cn, adOpenKeyset, adLockReadOnly, adCmdText
        .MoveFirst
        Do While Not .EOF
            Me.lstServiceType.AddItem .Fields(0)
            .MoveNext
        Loop
        .Close
    End With

    GatherWorkBookNames
    Me.Tag = 1
    LoadWorkBookToForm Me.Tag

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Workbooks(Me.Caption).Close False
    frmSwitchboard.Show   <<<<< SHOW THE FIRST FORM AGAIN.

End Sub

I'm guess that it's something to do with the last section of code, but I thought if I press the 'x' to close the form it would close it.
I've tried it with Screenupdating on & off and calculation on and off but it still gives the same error.

Any ideas???
Thankyou for any input!

 

[stuartford]

if you turn ShowModal to false in the properties then it will work with show and hide

hope this is of some help
Stuart

 

[stuartford]

before showing the original form try putting
UserForm1.Hide

or whatever youve called it

i just tried this with two forms and it works

form 1

UserForm2.Show

Unload UserForm1


form 2

UserForm2.Hide
UserForm1.Hide
UserForm1.Show



hope this helps


Stu

 

[Darren Bartrup]

Thanks for that Stu! You're first suggestion worked perfectly.

It has brought up another problem which was on my list of problems that would eventually need addressing:
The second form displays information from a secondary workbook. Leaving the two forms modeless means I can go to that workbook and update the information on it without closing the forms. So now I need to write some kind of class module which will emulate the Worksheet_SelectionChange event on that workbook and update my form to reflect the changes made.

I'll try to figure that one out over the weekend when I haven't got internet access