PDA

View Full Version : Solved: Stop pop-up when excel closes



Daxton A.
07-14-2004, 02:34 PM
I did a save on all my workbooks using a loop and when I did Application.Quit at the end of the code, it popped up the msg asking me if I want to save my workbook. How do u get excel to ignore that msg. I've done it b4 but its been a while back. And now I keep everything in case i need it in the future.

Thanx

HalfAce
07-14-2004, 02:43 PM
Application.DisplayAlerts = False
Will prevent the message from appearing.
Conversely, you just use Application.DisplayAlerts = True to re-enable the alert messages.

This help?
Dan

Daxton A.
07-14-2004, 02:50 PM
Thank You again Dan!

CBrine
07-15-2004, 07:33 AM
Daxton,
Just as an addition to the above posts. The DisplayAlerts causes the message to not appear, which causes Excel to automatically save the file. If that's what you want then everything should be OK, if you don't want to save the file, then additional code will be required to disable the save action.

Daxton A.
07-15-2004, 08:10 AM
What's the additional code? And thank you for responding w/this CBrine.

CBrine
07-15-2004, 08:46 AM
Daxton,
I may have been mistaken. I was thinking the activeworkbook.save, or activeworkbook.close commands would default to save. The application.quit seems to bypass the save when used with the displayalerts =false. I did some tests, and got some strange results though? When I programmed a button with the displayalerts =false and the application.quit, and ran it, it still prompted me for the save, but if i stepped through it, it didn't prompt for the save?(Only when something changed on the workbook).
Did your's do this? Anyone have any idea why this would be happening?

Daxton A.
07-15-2004, 08:56 AM
Mine did do this so I created the code:


Sub endModule()

For i = 1 To Workbooks.Count
Workbooks(i).Save
Next i

Application.Quit
End Sub



And it works fine now.

CBrine
07-15-2004, 09:21 AM
Daxton,
I did something close to yours. I set the saved flag to true, then excel will not prompt you to save and the application.quit will bypass the save. So if you want to not save the activeworkbook, you might want to try this. It will save each open workbook, except for the workbook that the code is executed on, prior to closing excel.


Private Sub CommandButton1_Click()
Dim wb As Workbook, wb2 As Workbook
Set wb = ActiveWorkbook
For Each wb2 In Application.Workbooks
If wb2.Name = wb.Name Then
wb2.Saved = True
Else
wb2.Save
End If
Next
Application.Quit
End Sub

lucas
07-15-2004, 11:28 AM
I have a workbook that gives users access to info and they can then print it out but when they close the book I don't want them to save any changes. I used this code from a button created from the control toolbox:


Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Application.Quit
End Sub

It just closes the program, no questions asked.

If you want to save the changes and close the book with no questions asked, try this:

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Variant
For i = 1 To Workbooks.Count
Workbooks(i).Save
Next i

Application.DisplayAlerts = False
Application.Quit
End Sub

Steve

parry
07-15-2004, 03:40 PM
Hi, just an observation for others who may be reading this thread. You need to be careful when turning off alerts as this may have undesirable affects depending upon what you are doing. The alerts are there for a reason so if you disable them then errors/alerts which you may wish to be advised of will not occur.

If Steve had used this code instead then this only closes the workbook and keeps Excel running so you run the risk of operating without the alerts.

Application.DisplayAlerts = False
ThisWorkbook.Close


Therefore you should turn alerts back on when youve finished your action. However, whenever you open Excel the alerts are 'reset' to True regardless whether they were False when you closed Excel. So Steves code is fine because he is closing the Application itself so things will return to normal. Sorry for butting in but I thought I should mention this.

Zack Barresse
07-15-2004, 03:44 PM
Picking up on Parry's fine post, with the workbook close code, you can specify whether or not you want to save that workbook with a true or a false following on the same line. This example would save the workbook on closing...

ThisWorkbook.Close True