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

pluginguin

New Member
Joined
Aug 10, 2016
Messages
21
Office Version
  1. 2019
Platform
  1. Windows
I have a workbook that, upon opening makes the application invisible and opens it's userform:
Code:
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:
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
Code:
Public TWB As Workbook
Public KeepOpen As Boolean


Form SUBS
Code:
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:
Code:
Private Sub Workbook_Open()
  Set TWB = ThisWorkbook
  KeepOpen = False
  Parent.Application.Visible = False
  UserForm1.Show
End Sub
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

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
Back
Top