Don't close workbook

[Davers]

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.

 

[Davers]

Well, me...one way to do it would be to use:

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)]

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:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If CloseWbk = False Then Cancel = True
End Sub

In Module1:

Public CloseWbk As Boolean

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

HTH

 

[Davers]

Brilliant!!!!!!!!! ray: Thank you very much!

Everyone have a good weekend!

Dave