PDA

View Full Version : Close ss with confirmation



Codeblue
06-26-2012, 07:46 AM
Is it possible to close a ss without it asking if you want to save changes? I tried doing a macro but closing the ss made the recorder go away.

mikerickson
06-26-2012, 07:57 AM
This will close, without asking, without saving any changes
With ThisWorkbook
.Saved = True
.Close
End With

This will close, saving any changes, without asking
ThisWorkbook.Close SaveChanges:=True

CodeNinja
06-26-2012, 08:00 AM
Codeblue,
The big question is do you want to save the workbook changes or not...

If you want to save the workbook changes then:
Sub test()
ThisWorkbook.Saved = True
ThisWorkbook.Save
ThisWorkbook.Close

End Sub

If you do NOT want to save the changes then

Sub test2()
ThisWorkbook.Saved = True
ThisWorkbook.Close

End Sub

Hope one of these answers your question.

Codeblue
06-26-2012, 08:49 AM
Yes, this is great stuff. I'm not using a button to close the ss just the X in the upper right corner so I'm not sure where to place the code.

If I put this in my routine that opens my ss my userform goes away, which I don't want, then I close my ss it still asks if I want to save.

I should mention I am using a userform

CodeNinja
06-26-2012, 09:15 AM
You could always put it in worksheet_change()

Codeblue
06-26-2012, 09:57 AM
I tried this but it doesn't work


Public Sub worksheet_change(ByVal Target As Range)
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub

CodeNinja
06-26-2012, 10:08 AM
Better yet... lol... do it before workbook close...

Go into vbe, double click on the workbook excel object enter this...

Just know that this does NOT save changes to the workbook... so you will loose your changes if you close the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub


Try it, see if it works for you.

Codeblue
06-26-2012, 11:59 AM
You may need to talk to me like a 5 yr old cause I don't got it.
By workbook excel object you mean in my project explorer the one that has a default name of Thisworkbook? I put the code in there but it still asks about saving it or not. It may be confused since I have a feature of opening a ss with data that opens on top of my ss with my form on it. It does work when closing that ss.

Perhaps I need to make the opened ss with data the active ss then when closing have the active one execute your code. Or am I all wet here.

CodeNinja
06-26-2012, 12:29 PM
Why don't you attach your workbook so I can take a look at it... maybe something is affecting it... If not, I can adjust and send so you can see where it goes..

You could attach it without any data...

Codeblue
06-26-2012, 01:45 PM
Here is my project I had to delete some company sensitive info so it is a bit of a mess. Thanks for taking the time to look at this.

CodeNinja
06-26-2012, 02:20 PM
Codeblue,

1- I removed ThisWorkbook.ActiveSheet from the workbook_beforeclose
2- in your user form frmmain, there is a close app... if you want this to close the entire excel program from there, you will need to close the workbook there...

a- I have added a commented out thisworkbook.close which you can remove the ' and then this will close the workbook under that condition.
The way this is set now, if you close the workbook, it will not ask for a save, and it will not save the workbook automatically. This can result in lost data.

Hope this helps.

8330