Help! Save message!

robfo0

Active Member
Joined
Feb 19, 2002
Messages
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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
 
Upvote 0
Why would you want to bypass Excel's built in Save As? I'm smelling moronic boss or equally stupid client on this one.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top