Prohibit saving of Workbook on Close

Sandra Smith
Sandra Smith used Ask the Experts™
on
I have a reporting workbook that users open and can view various PivotTables.  Everything is done in code.  But while the workboko is open, they can create their own pivot tables and analysis.  However, when the workbook is closed, I DO NOT want any changes they have made to ever be saved.  I have the procedure below, but where should it be called to prevent any saving?

Sandra

Sub CloseNoSave()
     'Close the workbook without saving it
    ThisWorkbook.Close savechanges:=False
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer

Commented:
http://support.microsoft.com/kb/129153

Sub Auto_Close()
       ThisWorkbook.Saved = True
'or call your CloseNoSave function here.
   End Sub
Top Expert 2010
Commented:
If you do not want users to be able to save the document, Auto_Close will not help.

You could use the Workbook_BeforeSave event to do this, but understand that that will work if and only if macros are enabled, and most of the time users can disable macros if they choose to do so.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Why not just make the workbook read-only?  Then, the users can only save the file as another name or to another folder, but they won't be saving on top of your production copy.

Only by eliminating the save option (either through the method matthewspatrick proposed, or by eliminating the option altogether - if you have Excel 2007/2010 - via Ribbon modifications) can you prevent the workbook from being saved somewhere else.

If you are interested in Ribbon mod's, let me know.

Dave
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Sandra SmithRetired

Author

Commented:
This is an Excel 2010 workbook.  I do want users to be able to change the pivot tables and create their own if necessary while they are in the workbook, but I do not want them making their own copies at all.  I don't think read-only would work.  I like the idea of the Ribbon mod approach however.  I did hestiate to use the BeforeSave as I have had user disable macros, which kinda defeated the purpose of an automated workbook.

Sandra
Most Valuable Expert 2012
Top Expert 2012

Commented:
You can make the modifications according to Ron deBruin's excellent site on the topic:http://www.rondebruin.nl/ribbon.htm

Take a look at this solution and see if its going in the direction you want.

It disables a bit more than just the save function, but if you start to read the XML code, you can see how that can be changed to include other menu options.  Take a look, download the example, and advise if this is what you want, and I can customize it from there:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27721944.html

Cheers,

Dave
Sandra SmithRetired

Author

Commented:
I will take a look and get back to you.  Thank you for the direction.
Sandra
Sandra SmithRetired

Author

Commented:
Darn, you suggestions download something that I cannot.  I am at a financial institutions and we cannot load or add anything to our machines.  So, I must do this in code.

Sandra
Sandra SmithRetired

Author

Commented:
Actually, I reversed this a bit.  In the open event, I delete any worksheets other than a secific few and if the users have deleted those, then I create them.  Works.

Sandra
Most Valuable Expert 2012
Top Expert 2012

Commented:
Actually, you can build the RibbonX modifications without downloading anything.

First, you need the CustomUI editor, so you can open a workbook to get at its XML.  Then, you can copy/paste the XML I gave in the example post, save and test for yourself.

Check Ron's website for downloading the editor and how to use it:
http://www.rondebruin.nl/ribbon.htm

Otherwise, you are left with using BeforeSave() code, as previously suggested, but it can be mangled by users, as was noted.

Dave
Sandra SmithRetired

Author

Commented:
I believe your suggestion would have been a good solution, but where I am does not permit downloads from the internet of any sort.  however I am going to try it out on my home machine.  will post how it turns out.

Sandra

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial