Close workbook when UserForm is closed, or close excel when no other workbooks are active

[pluginguin]

I have a workbook that, upon opening makes the application invisible and opens it's userform:

Private Sub Workbook_Open()
Parent.Application.Visible = False
UserForm1.Show
End Sub

There is a button on the form to unhide the parent application again, in order for users to modify the sheets.

When I close the userform using it's X in the top right corner I want it to check two things.

1. If the parent.application is visible it should do nothing. Otherwise check 2.
2. If there are more workbooks open it should only close the workbook with the userform and set focus on next workbook, otherwise close excel completely

Currently I'm using this code:

Private Sub UserForm_Terminate()


    If Parent.Application.Visible = False Then
        If Application.Workbooks.Count < 2 Then
            Parent.Application.Visible = True
            ActiveWorkbook.Close SaveChanges:=False
            Application.Quit
        Else
            Parent.Application.Visible = True
            ActiveWorkbook.Close SaveChanges:=False
        End If
    Else
    Exit Sub
    End If
End Sub

But point 2 it is not working.
If the parent application is invisible and I have another workbook open: it closes the other workbook and makes itself visible.
If the parent application is invisible and no other workbooks are open: it closes itself but leaves the application running.

What do I have to change to get it to work?
Thx in advance!

 

[Jeffrey Mahoney]

Here is one way:

The first three subs are from the user form. A button to show the workbook. A button to close the form and close the workbook. Th Sub Closeform_btn just unloads the form. The sub that's doing all the work is the UserForm_Terminate(). That runs if you press the CloseForm button or just click the X to close the form.

You will need to create two new variables in a normal module.

Public TWB As Workbook
Public KeepOpen As Boolean

Form SUBS

Private Sub CloseForm_btn_Click()
  Unload UserForm1
  
End Sub


Private Sub ShowWorkbook_btn_Click()
  Application.Visible = True
  KeepOpen = True
  Unload UserForm1
  
End Sub




Private Sub UserForm_Terminate()
  If KeepOpen = False Then
    Application.Visible = True
    If Application.Workbooks.Count < 2 Then
      Application.Quit
    End If
    TWB.Close SaveChanges:=False
  End If
End Sub

Finally, this is how you would start:

Private Sub Workbook_Open()
  Set TWB = ThisWorkbook
  KeepOpen = False
  Parent.Application.Visible = False
  UserForm1.Show
End Sub

 

[pluginguin]

Thanks Jeffrey,

This is exactly what I wanted. It works!
I already had other things in place for which you imagined those buttons, but changing the workbook_open event, the module and the userform_terminate event did the trick.
Thank you very much.

Pluggie

 

[Jeffrey Mahoney]

You're welcome