Help! Save message!

robfo0 · Apr 24th, 2002, 05:31 PM

Hi everyone,

I have some code that SHOULD ask the user to save the workbook with the beforeclose event. The message does come up, but the problem is, if the user selects "Yes" on the msgbox, the code saves, but right after that, excel asks with ITS message if the user wants to save the changes. I dont want the 2nd (excel's) save message to come up. Here is my code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set DataSheet = Worksheets("DataSheet")

opensheet = ""
'Call TestOpenSheets
Application.Run "Navigation.TestOpenSheets"

If Left(ActiveWorkbook.Name, 6) = "Retail" Then

'code

Else
'code

If Left(CurrFile, 6) = "Retail" Then Exit Sub

Application.ScreenUpdating = False

CurrFile = ActiveWorkbook.Name

'code

Application.DisplayAlerts = False
DataSheet.Delete
Worksheets("Navigation").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Disables Counted Shutdown Before closing
Call Disable
ActiveWorkbook.Save

Else

Application.DisplayAlerts = False
DataSheet.Delete
Worksheets("Navigation").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Disables Counted Shutdown Before closing
Call Disable
ActiveWorkbook.Saved = True
End If

ActiveWorkbook.Saved = True
End If

End Sub

Why is the message coming up? i am telling excel the workbook IS saved, yet it still asks??

Can i accomplish this with application.displayalerts=false? If i use this, does the file save or NOT save when overriding? also, do i have to turn alerts back on or do they automatically come back on? (for example if i have more than one file open and i want alerts ON in that one)

Thanks

Tom Urtis · Apr 24th, 2002, 08:23 PM

Try inserting this at the end of your code, and/or at the end of your If blocks, depending on when you want your code to handle the actual save process:

Cancel = True

Mark O'Brien · Apr 24th, 2002, 10:28 PM

Why would you want to bypass Excel's built in Save As? I'm smelling moronic boss or equally stupid client on this one.

robfo0 · Apr 25th, 2002, 12:16 AM

Mark, lol, actually the reason im doing it is im using code TStom gave me to export information to a text file on the close event. but i want to make sure the user WANTS to save the changes. if i dont ask the question, the export will occur, then save regardless of if the user clicks yes or no to excel's save question becuase the information will export to a text file. If i ask the question, i can control the export.

But i guess it does boil down to a boss, the reason im exporting is because he wants to run reports like in access, but i dont know access, so im using text files to do it

tom, i will try your idea, i dont have the file with me. But can you answer my other questions? if i disablealerts before the save, and then DONT re-enable, will it prevent other open workbooks from displaying alerts or is the displayalerts method only applicable to the workbook its ran from?

Thanks

[ This Message was edited by: robfo0 on 2002-04-24 23:16 ]

robfo0 · Apr 25th, 2002, 12:29 AM

Tom, how do I use the cancel method?

I'm trying a sample with this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
savetest = MsgBox("save?", vbYesNo)

If savetest = vbYes Then
ActiveWorkbook.Save

Else
End If
ActiveWorkbook.Saved = True
Cancel = True

End Sub

This prevents the message from coming up, but it also prevents the file from closing at all!...am i doing something wrong?

Tom Schreiner · Apr 25th, 2002, 12:47 AM

Ho Rob
Application.DisplayAlerts applies to the entire Excel application. Resets to deault(False) if the app is closed and then re-opened...
Also in the:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
event...
If Cancel = True then you are cancelling the Close Event.
The workbook will cannot close.
If the user does not want to save the workbook, then close with this line.
Activeworkbook.Close SaveChanges: = False
Hope this helps.
I am not exactly clear on what you are doing???
Tom

robfo0 · Apr 25th, 2002, 02:37 AM

TSTom, maybe you can help me. What i am doing is using the before_close event to export my data you helped me with to a text file. (i made this process automactic, it imports it on open, and exports on before close).

If for example, excels save dialog came up when i closed the file, but i did not want to save my changes, i would click no, BUT, since my text export is the before_close, it would still export the information even if i dindt want to save it, thus saving it anyway.

This is what i am trying to prevent using vbyesno msgbox. if the user clicks yes, it proceeds with the text export, and saves. if they click no, it does not export and i use activeworkbook.saved=true. I have tried simple examples like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
savetest = MsgBox("save?", vbYesNo + vbQuestion, "Save " & ActiveWorkbook.Name)

If savetest = vbYes Then
ActiveWorkbook.Save

Else
End If
ActiveWorkbook.Saved = True

End Sub

and it DOES what i want it to do, but in the code of my actual workbook (which resembles the code i first posted in this string), it does NOT work. It will ask the question, but then it ALSO asks again if i want to save, which i would like to prevent. I'm really not sure why it is doing this, i have tried putting a few activeworkbook.saved=true in a few places, but it still does it. after that last activeworkbook.saved=true, there are NO other changes, so why does it still ask the question? any ideas?

Thanks

[ This Message was edited by: robfo0 on 2002-04-25 01:43 ]

Apr 24th, 2002, 08:23 PM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,388	Try inserting this at the end of your code, and/or at the end of your If blocks, depending on when you want your code to handle the actual save process: Cancel = True __________________ Tom Urtis Microsoft MVP - Excel

Apr 24th, 2002, 10:28 PM	#3
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Why would you want to bypass Excel's built in Save As? I'm smelling moronic boss or equally stupid client on this one. __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 05:31 PM	#1
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	Hi everyone, I have some code that SHOULD ask the user to save the workbook with the beforeclose event. The message does come up, but the problem is, if the user selects "Yes" on the msgbox, the code saves, but right after that, excel asks with ITS message if the user wants to save the changes. I dont want the 2nd (excel's) save message to come up. Here is my code: Private Sub Workbook_BeforeClose(Cancel As Boolean) Set DataSheet = Worksheets("DataSheet") opensheet = "" 'Call TestOpenSheets Application.Run "Navigation.TestOpenSheets" If Left(ActiveWorkbook.Name, 6) = "Retail" Then 'code Else 'code If Left(CurrFile, 6) = "Retail" Then Exit Sub Application.ScreenUpdating = False CurrFile = ActiveWorkbook.Name 'code Application.DisplayAlerts = False DataSheet.Delete Worksheets("Navigation").Delete Application.DisplayAlerts = True Application.ScreenUpdating = True 'Disables Counted Shutdown Before closing Call Disable ActiveWorkbook.Save Else Application.DisplayAlerts = False DataSheet.Delete Worksheets("Navigation").Delete Application.DisplayAlerts = True Application.ScreenUpdating = True 'Disables Counted Shutdown Before closing Call Disable ActiveWorkbook.Saved = True End If ActiveWorkbook.Saved = True End If End Sub Why is the message coming up? i am telling excel the workbook IS saved, yet it still asks?? Can i accomplish this with application.displayalerts=false? If i use this, does the file save or NOT save when overriding? also, do i have to turn alerts back on or do they automatically come back on? (for example if i have more than one file open and i want alerts ON in that one) Thanks

Apr 25th, 2002, 12:16 AM	#4
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	Mark, lol, actually the reason im doing it is im using code TStom gave me to export information to a text file on the close event. but i want to make sure the user WANTS to save the changes. if i dont ask the question, the export will occur, then save regardless of if the user clicks yes or no to excel's save question becuase the information will export to a text file. If i ask the question, i can control the export. But i guess it does boil down to a boss, the reason im exporting is because he wants to run reports like in access, but i dont know access, so im using text files to do it tom, i will try your idea, i dont have the file with me. But can you answer my other questions? if i disablealerts before the save, and then DONT re-enable, will it prevent other open workbooks from displaying alerts or is the displayalerts method only applicable to the workbook its ran from? Thanks [ This Message was edited by: robfo0 on 2002-04-24 23:16 ]

Apr 25th, 2002, 12:29 AM	#5
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	Tom, how do I use the cancel method? I'm trying a sample with this code: Private Sub Workbook_BeforeClose(Cancel As Boolean) savetest = MsgBox("save?", vbYesNo) If savetest = vbYes Then ActiveWorkbook.Save Else End If ActiveWorkbook.Saved = True Cancel = True End Sub This prevents the message from coming up, but it also prevents the file from closing at all!...am i doing something wrong?

Apr 25th, 2002, 12:47 AM	#6
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Ho Rob Application.DisplayAlerts applies to the entire Excel application. Resets to deault(False) if the app is closed and then re-opened... Also in the: Private Sub Workbook_BeforeClose(Cancel As Boolean) event... If Cancel = True then you are cancelling the Close Event. The workbook will cannot close. If the user does not want to save the workbook, then close with this line. Activeworkbook.Close SaveChanges: = False Hope this helps. I am not exactly clear on what you are doing??? Tom

Apr 25th, 2002, 02:37 AM	#7
robfo0 Board Regular Join Date: Feb 2002 Location: Stockton, California Posts: 281	TSTom, maybe you can help me. What i am doing is using the before_close event to export my data you helped me with to a text file. (i made this process automactic, it imports it on open, and exports on before close). If for example, excels save dialog came up when i closed the file, but i did not want to save my changes, i would click no, BUT, since my text export is the before_close, it would still export the information even if i dindt want to save it, thus saving it anyway. This is what i am trying to prevent using vbyesno msgbox. if the user clicks yes, it proceeds with the text export, and saves. if they click no, it does not export and i use activeworkbook.saved=true. I have tried simple examples like this: Private Sub Workbook_BeforeClose(Cancel As Boolean) savetest = MsgBox("save?", vbYesNo + vbQuestion, "Save " & ActiveWorkbook.Name) If savetest = vbYes Then ActiveWorkbook.Save Else End If ActiveWorkbook.Saved = True End Sub and it DOES what i want it to do, but in the code of my actual workbook (which resembles the code i first posted in this string), it does NOT work. It will ask the question, but then it ALSO asks again if i want to save, which i would like to prevent. I'm really not sure why it is doing this, i have tried putting a few activeworkbook.saved=true in a few places, but it still does it. after that last activeworkbook.saved=true, there are NO other changes, so why does it still ask the question? any ideas? Thanks [ This Message was edited by: robfo0 on 2002-04-25 01:43 ]