Forum Discussion
Excel - disable "edit anyway" for read-only file
thank you for the response. but i don't want them to need a password to use the spreadsheet (to modify the contents of cells), i just don't want them to be able to save those modifications to the same file name.
If you specify a "password to modify", users can still open the workbook without needing the password. In that situation, the workbook will be read-only for them without an option to edit, just like you want.
But those who need to edit the workbook will have to provide the password.
- Thank you for your comment. But I think perhaps I am not explaining my problem very well. I WANT them to be able to edit the workbook; i just don't want them to be able to save their edited version over my original.
When my users open the workbook, they get the yellow warning at the top "to prevent accidental changes, the author has set this file to open as read-only," with the "Edit Anyway" box next to it. If they hit "edit anyway", then they can edit the workbook (like I want them to) but their changes are auto-saved on OneDrive. If they IGNORE the "edit anyway" button or hit "X" at the top right of the yellow warning message, then everything works exactly like I want it to. They can edit the cells of the workbook (like I want), but it does not auto-save, and when they close out of the workbook they get a prompt asking them if they want to save. They can save it, but it forces them to change the name. I want them to be able to edit, i just don't want them to save any changes to my original file. Thanks again for your help.When the user opens the document as read only, they can still edit the workbook.'But trying to save the workbook will cause Excel to display a warning:
After that, the Save As dialog will be displayed.
Another option would be to save the workbook as an Excel template. Excel will then create a new copy when the user double-clicks the template.
HansVogelaar I had the same problem, my way around this was to open the file as read only from another macro enabled document.
In the launcher workbook, I just put a quick macro in the workbook_open event like this:
Private Sub Workbook_Open() Workbooks.Open ("location of the workbook you want opened as read only"), ReadOnly:=True End SubIf that workbook has macros, and you want these enabled on opening (depends on your security settings, and if they're set by the company), you can add the following lines:
Private Sub Workbook_Open() ' Change Security Setting to enable macros on startup without prompt Application.AutomationSecurity = msoAutomationSecurityLow Workbooks.Open ("location of the workbook you want opened as read only"), ReadOnly:=True ' return to default security setting Application.AutomationSecurity = msoAutomationSecurityByUI End Sub
