Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

Prohibit saving of Workbook on Close

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
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

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

Sub Auto_Close()
       ThisWorkbook.Saved = True
'or call your CloseNoSave function here.
   End Sub
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Sandra Smith

ASKER

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
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:

https://www.experts-exchange.com/questions/27721944/Excel-2010-Control-IDs.html

Cheers,

Dave
I will take a look and get back to you.  Thank you for the direction.
Sandra
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
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
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
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