Macro to turn off save box when user click close button

[Faridwahidi]

Hi,

I noticed that when we use the following function

OFFSET(),
NOW(),
TODAY() and so on

the formula is running and calculating every time workbook is opened . This will cause save box pop up when user click on closing button. How to apply workbook change event to turn off the save box?

I guess like this but not sure how to apply? any help would be much appreciated

Please Login or Register  to view this content.

[JLGWhiz]

Not sure what you mean by "Save Box". If a user makes changes to a workbook and clicks the close button without saving, Excel automatically displays a dialog box asking if the user wants to save the changes they made. If you disable that feature, then changes that the user wants to save could get discarded. If you are getting the SaveAs dialog box displayed, then it is because the user has created a new file and Excel does not know what to do with it. But that would only appear if the user clicked "Save", not the close button. Both of these features are built into the system software to prevent loss of data by the user forgetting to save their work, or to allow them the option to discard their changes. You could set the calculation to manual, but then you would need to remember to run it mannually to get any values calculated.

Maybe I don't understand the problem.

[Faridwahidi]

Hi,

If you apply formula OFFSET function in your worbook, you will be prompted with save dialog box even though there is no changes made in the workbook.

one more problem, even though you have just click on save button, you will be prompted again when click on closing button. Thus, I want to disable it

[AndySimm2000]

Hi - If you want to disable the "do you wish to save" altogether then I use a "Auto _close" macro that turns off any prompt for saving...

Please Login or Register  to view this content.

Hope that helps- remember it closes the sheet without prompt to save whatever you do so be careful!!

Andy

[Keebellah]

Of course if you want the workbook to be saved too then...

Please Login or Register  to view this content.

If the workbook is not read only the workbook will be saved when closing

BWT: this is air code, not tested, but it's the idea

[Faridwahidi]

hi,

The code above turn off dialog box BUT automatically save the workbook when user click on closing button. I don't want it to be automatically save to avoid incident of user made wrong changes.

any help please

[Keebellah]

The just add a msgBox asking the user if he/she wishes to save.
It's not THAT difficult

[AndySimm2000]

Hi the code that is below closes, without saving or prompting for saving..

Please Login or Register  to view this content.

Andy

[AndySimm2000]

Hi the code that is below closes, without saving or prompting for saving..

Please Login or Register  to view this content.

Andy

[Keebellah]

That is what the poster wants: HE DOES NOT ALWAYS WANT TO SAVE THE FILE....

Please Login or Register  to view this content.

[Faridwahidi]

hi Keebellah,


You have created the code same as without VBA. Have you test it before?

when I click on closing button, there is a pop up dialog box ask me to save or not.

I do NOT want dialog box to pop up and I do NOT want it to save automatically. if you cannot resolve it, just ignore the thread, let others help.

[Keebellah]

I'll let others help.
Small suggestion: start learning VBA yourself
It's not that difficult, I learned.

[Faridwahidi]

Hi Keebellah,

Thanks for suggestion, I have started learning VBA after register in this forum over the past 2 years. Beginning last year, I have been trying to help VBA new entry level to resolve their problem. Approximately 15% of 591 my post were to help others rather than only asking question. Besides this forum, I have user account in MrExcel, Ozgrid and VBA Express. I have also manage to create complex Userform database for my department in my job.

you can see over here
http://www.excelforum.com/showthread...=1#post4322173
http://www.excelforum.com/showthread...=1#post4341349
http://www.excelforum.com/showthread...=1#post4337404
http://www.excelforum.com/showthread...=1#post4336908
http://www.excelforum.com/showthread...=1#post4341256
http://www.excelforum.com/showthread...=1#post4334744
http://www.excelforum.com/showthread...=1#post4326429
http://www.excelforum.com/showthread...=1#post4167029
http://www.excelforum.com/showthread...=1#post4152677
http://www.excelforum.com/showthread...=1#post4160879
http://www.excelforum.com/showthread...=1#post4214116
http://www.excelforum.com/showthread...=1#post3976090
http://www.excelforum.com/showthread...=1#post4210032

[Chris McGlothen]

I can make it work by using the code in this attached file. I would just copy the macros over and paste the code into the worksheet. Hope this helps you.

[Keebellah]

@Fariwahidi, congratulations, keep it up, especially your comments are really stimulating
I have only been able to anser and help with ~6000 posts.

[JLGWhiz]

@Faridwahidi, as I tried to point out in my first post, you are fighting agaist the system software which is designed to prevent users from inadvertantly losing data by prematurely closing a file. So, the only way you will avoid the safety messages, is to fool the underlying system software into thinking you have done what needs to be done before closing a workbook. If you have your calculation set to automatic, and you have certain functions in your worksheet, like those you listed, then any change of any type will activate a calculation and set the save warning trigger. To do what you want, you will probably have to set calculation to manual, then use code similar to that posted by Chris McGlothen to put the file in a saved status. But to me, it is much simpler to just click the response buttons on the alert dialog box. If you disable the alert, there is a greater chance that your users, including you, will inadvertantly lose data because the file was not saved.

[vcoolio]

Hello Faridwahidi,

I'm with JLGWhiz on this one. Have a look at this similar thread:-

http://www.excelforum.com/showthread...69#post4006369

and then read through the thread mentioned in post #5, especially the very last post in that thread.

You may then understand the complexities of what you are asking and the round-about issues that will arise.

Cheerio,
vcoolio.