Avatar of Sandra Smith
Sandra Smith
Flag 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
Microsoft DevelopmentMicrosoft ApplicationsMicrosoft Excel

Avatar of undefined
Last Comment
Sandra Smith

8/22/2022 - Mon
Kyle Abrahams

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

Sub Auto_Close()
       ThisWorkbook.Saved = True
'or call your CloseNoSave function here.
   End Sub
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dlmille

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
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dlmille

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/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27721944.html

Cheers,

Dave
Sandra Smith

ASKER
I will take a look and get back to you.  Thank you for the direction.
Sandra
Sandra Smith

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sandra Smith

ASKER
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
dlmille

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 Smith

ASKER
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck