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.
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.
ASKER
Sorry if this is a stupid question... but how do I do that?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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??
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??
ASKER
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
"Then click on ThisWorkbook(on left windows)" I don't see anything like that.
This is what I see.
VB-window.JPG
Click on arrow where it says(in picture) Sheet1 Workseet and select workbook
ASKER
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.
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.
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.
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.
ASKER
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!
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!
You are welcome!
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub
This will prevent users from saving the data.