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

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
Option Explicit

Private Sub Workbook_Open()

    OpenForm

End Sub
In a module:
Code:
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:
Code:
Private Sub cmdProcess_Click()

    Load frmProcessReturns              <<<<<< LOAD THE SECOND FORM.
    frmProcessReturns.Show             <<<<<< SHOW THE SECOND FORM.
    
End Sub
In frmProcessReturns form:
Code:
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
Code:
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

stuartford

Active Member
Joined
Nov 28, 2002
Messages
419
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

Active Member
Joined
Nov 28, 2002
Messages
419
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

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Thanks for that Stu! (y) 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 :(
 

Forum statistics

Threads
1,143,637
Messages
5,719,972
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top