Don't close workbook

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Hi everyone. I have a master reporting workbook that houses all my reports that need to be done in the morning...it runs them, zips them, e-mails them, deletes some, creates new, archives files,...etc....It's been fun creating. However, when I use this to open a template to create a new report, if I close the new workbook using the X in the far upper right corner, it closes my master reporting workbook as well...What I'm wondering is if there's a way to keep a workbook from closing...other than using that other X. I'm thinking of other people using this and accidentally closing the workbook the wrong way and having to re-open the master...not a big deal, but I'm trying to make this as idiot proof as possible...

Any ideas? I'd rather not have to modify all of my reports...(roughly 12) with something like a button that does an activewindow close...I'd rather my one workbook refuse to close unless I use a button with code to close it...any ideas???

Thanks,

Dave M.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Well, me...one way to do it would be to use:

Code:
Private Sub Workbook_BeforeClose(cancel As Boolean)
     cancel = True
End Sub

this effectively keeps me from closing the workbook using the X or any other method...but then I'm not sure how to close it using code....

Any ideas yet?

Thanks,

Dave M.
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Dave,

You were nearly there!

Try using a Public boolean variable to indicate whether the Cancel is used, then set it to True in your own routine. Like this:

In the ThisWorkbook module:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If CloseWbk = False Then Cancel = True
End Sub
In Module1:
Code:
Public CloseWbk As Boolean

Sub CloseMe()
    CloseWbk = True
    ThisWorkbook.Close
End Sub
HTH
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
Brilliant!!!!!!!!! :pray: Thank you very much!

Everyone have a good weekend! (y) :p

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

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