Closing workbook without close button

[Juriemagic]

Hi good people!,

I have posted yesterday on Mrexcel, but to date no replies. The link is: http://www.mrexcel.com/forum/excel-q...se-button.html

There are literally hundreds of threads on this topic, BUT..(Always a "BUT"), the circumstances surrounding them are all different to mine, some of them give replies, but for some reason do not work when I implement them..therefore the thread.

I have two codes: one in the "This workbook":

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Visible = True
    If CloseMode = 0 Then Cancel = True
    
End Sub

and a userform with this code:

ActiveWorkbook.Close SaveChanges:=True

The userform DID work, until I disabled the workbook close button. How can I amend the code in the userform to close the workbook?. I have tried:

If CloseMode = 1 Then Cancel = False
    ActiveWorkbook.Close SaveChanges:=True

but get an error "variable not defined"...please help...thank you all very kindly..

[Aflatoon]

CloseMode is a variable supplied by the userform QueryClose event - it has no meaning outside that routine, so will not work in a Workbook_BeforeClose event. What do you intend it to do?

[Juriemagic]

Hi,

Closing the workbook the normal way should be disabled, because there is no guarantee the users will actually save the latest work. So I have created the userform to save and close.

[Aflatoon]

I think you missed my point. You don't seem to have declared CloseMode anywhere, or assigned a value to it, so it will always prevent closing the workbook with your current code. You need to declare it so that it is visible to both routines and assign it a value other than 0 when you want to be able to close.

[Juriemagic]

Yes, I do understand that, but this is where the problem comes in..I do not know how to do it..How do I declare, declare as what?...I have also tried to read up on this but without any luck..please help me with how the code should look?

[Aflatoon]

In a normal module:

Public bLetClose as Boolean

In ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)    Application.Visible = True
    If Not bLetClose Then Cancel = True
End Sub

In the userform:

bLetClose = True
ActiveWorkbook.Close SaveChanges:=True

[Juriemagic]

Okay, I have done precisely as you indicated. When I clicked the normal close button, the sheet disappeared but Excel stayed open. The screen flickered and I noticed 2 extra excel books had opened (grey screens). Upon trying the usual way again, the button now seemed to be disabled. I then tried my own button, but nothing happens. Maybe just something small somewhere?..Please see if you can assist..

[Aflatoon]

Please post the exact codes you have and state where they are. Better yet, post a workbook.

[snb]

To me this seems suffieient, no other code needed.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ThisWorkbook.Close -1
End Sub

[Juriemagic]

Hi Aflatoon,

I have tried to send workbook, but application to upload, fails. Anyways, may I suggest please?. Just open a new spreadsheet and insert your codes as in post #6. Then try it out.

I have used your codes in a new workbook. What happens is the following: After codes are inserted, when I click the standard close button, The sheet closes but excel remains open. When I click the excel icon on my task bar, I see an excel file with the name "book 9" (The name of my new workbook), but in addition to that, I see two more excel icons. Clicking on either of them only opens a grey screen. Okay now, when I click the "book 9" (in my case), the sheet maximizes. NOW, if I click the standard close button, it does not work (which is what is supposed to be from the very start already)..BUT sadly, the userform also does not work. The ONLY way to get rid of these files is to restart the pc..

The codes are EXACTLY as you gave them to me. Maybe just to show the code in the userform:

Sub test()
'
' test Macro
'
'
    bLetClose = True
ActiveWorkbook.Close SaveChanges:=True
End Sub

I really hope you can help..

[Juriemagic]

snb, are you saying get rid of the other codes?...how will the standard close button be disabled then?.

[snb]

Exactly.
There's no need to disable anything if you use that code.

[Aflatoon]

Just open a new spreadsheet and insert your codes as in post #6. Then try it out.

Done, and it behaves as requested, at least in 2010. I will try and test in 2013 later on.

[Juriemagic]

I am sure it must be a 2013 issue...I appreciate your time with this..