Macro to turn off save box when user click close button

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.

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.

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

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

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

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

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

Please Login or Register  to view this content.

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.

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

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

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

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.