Closing Workbook when Userform closes

[Boechat]

Greetings everyone,

I'm currently trying to get my excel application to close whenever I close any userform using the red X in the right hand corner. Besides, I'd like the form to throw a prompt asking whether I want to save it or not.
How do I get this done without having to code it into every single form I have (about 40 in this program)?

Thanks in advance

 

[RedBeard]

I don't know how to do it once for every single form with one code snippet (someone smarter might know?), but try:

Private Sub UserForm_Terminate()ThisWorkbook.Close
Application.Quit
End Sub

 

[mikerickson]

To detect if you are using a corner X vs. a button that unloads the user form, you have to use the Userform_QueryClose event.
So it sounds like 40 QueryClose events are in order.

 

[Boechat]

Well, 40 it is then! haha
Thanks, guys.

@mikerickson How would that code look like? I need it to close the form and the excel workbook (that isn't visible) and also throw a prompt asking whether to save it or not.

 

[mikerickson]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormCode Then
        MsgBox "closed via code"
    ElseIf CloseMode = vbFormControlMenu Then
        MsgBox "closed corner X"
    End If
End Sub

 

[Boechat]

Hey again, sorry for asking again, I'm still quite new to this and might not have expressed myself clearly.

What I want is: if the form is closed using the red X, the workbook closes as well and I get a prompt asking whether I want to save the changes or not. If the form is closed using a command button or anything else, I want nothing else to happen, with no prompts saying which way they were closed.

It's basically a code to close form and workbook together when you hit the red X and save the changes if wished.

Thanks for all the support and sorry for the disturbance!

 

[mikerickson]

What code have you tried?

 

[Boechat]

so far this one:

Private Sub UserForm_Terminate()ThisWorkbook.Close
Application.Quit
End Sub

But it doesn't seem to differenciate between the way you close the form. I hoped there was a way to merge the one you mentioned, with query close and the one I tried before

 

[Norie]

You can't really 'merge' userform events.

If you want something to happen based on how the user is closing the userform you have to use QueryClose.

By the way, 40 userforms? Is that 40 userforms in one workbook?

 

[Boechat]

Hey, Norie

Yes, they are all in the same workbook.

Perhaps merge isn't the right word, I was thinking something like:

[COLOR=#333333][FONT=Verdana][INDENT]Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        ThisWorkbook.Close
        Application.Quit
    End If
End Sub
[/INDENT]

[/FONT][/COLOR]

However I'm not sure whether this will also cover the "Do you want to save the changes?" prompt