Restrict Saving changes to an Excel document

I have an excel worksheet that allows users to enter personal information and then it works out a $$ rate based on that personal information.   This document is going to be made available to everyone in the company so they can each use it to inquire what their rate will be.  I do not want them to be able to save this document, because when the next person opens the document the first persons personal information will be there.
I have tried "Mark as Final" so they can not save any changes to the document but when I do that they are unable to make any changes either... which means they cant enter their information in the first place...
Is there another option that I could try?
Thank you for your help.
KAOSSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
Just add this code to your Workbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Cancel = True

End Sub


This will prevent users from saving the data.
0
KAOSSAuthor Commented:
Sorry if this is a stupid question... but how do I do that?
0
mukulguptaCommented:
Hi KAOSS,

There are many ways to code into excel so that the file cannot be saved. But to do so you need to make the excel sheet a macro using Microsoft's Visual Basic Editor. You would need the software and a little bit of coding knowledge.

Example code for disabling save option:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    MsgBox "You can't save this workbook!"
    Cancel = True

End Sub
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

unknown_routineCommented:
No its not a stupid question at all if you are not familiar with VBA.

For this you have to enable the Excel Developer Tab.

Then click on it and then click on visual basic icon.


Then click on ThisWorkbook(on left windows) and copy and paste the code I gave you to the bottom or right window.


If you do not see the developer Tab , go to Excel options , popular, and check "Show Developer tab in the ribbon"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KAOSSAuthor Commented:
This may be a little over my head.  I do have the developer tab with Visual basic.. but I have no idea how to use it..

Is it something simple that you could give me the steps to do? or should I go looking for a visual basic for dummies tutorial somewhere??
0
KAOSSAuthor Commented:
I have clicked on the VB icon and the VB window is open but Im not sure what you mean by :
"Then click on ThisWorkbook(on left windows)"  I don't see anything like that.
This is what I see.
VB-window.JPG
0
unknown_routineCommented:
Click on arrow where it says(in picture) Sheet1 Workseet and select workbook
0
KAOSSAuthor Commented:
I have gotten a little further... I have found  "this workbook" and pasted the code and it works fine.. when I hit save it says you cant save this file. Perfect
The only problem is when I reopen the workbook, it hasn't saved the "don't save" macro...

How do I save the "don't save" macro in the document before I close it.
0
unknown_routineCommented:
This is a good question!

go to VBA  where you see


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Cancel = True

End Sub


Click on left bar  of

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

You will see a Red dot(break point)


Close the Developer and save the file. You will see code stops on that red circle  and there is a yellow arrow on that circle. Click and DRAG the yellow arrow Over
Cancel=True and leave in on End Sub.

So Cancel-true won't run. Cancel=true prevents you from saving.
0
KAOSSAuthor Commented:
I have done that... and it still wouldn't work..
I believe I may have figured it out.. I was not in design mode when I was trying to save the code... looks like its working now.. Thank you so much for your help and the step by step instruction.   I never would have been able to do this on my own!
0
unknown_routineCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.