Nov 06 2023 06:25 AM
Nov 06 2023 06:25 AM
Good afternoon to all Excel community,
I want to protect an Excel file like this :
With my team we will be able to freely open the document. But if we have to save it (most of the time we will only read, change some data without saving these modifications), I want Excel to suggest a password. With this when we will close the file, if we made some unwanted changes, it will remind us not to save. (with only popup like "do you want to save", sometimes we clic on "yes" instead of "no" by mistake).
I already think of the solutions below that doesn't fit :
- Protect the file by a password : it will only protect the opening : as we will all have the password, it will not prevent us to save an unwanted change.
- Protect the modification of the worksheet by a password : same as above, we often need to make some modification and it is important to allow that. But we must protect the saving part only. If we put the passeword at the beggining, working on the sheet for some hours, we will forget and be able to save it by mistake.
- Open as "read-only" documents. In case of wanted modifications that we want to save, we will have to redo everything not in the "read-only" option right ?
We work on Windows 10 / Excel by Microsoft 365 for companies (version 2205)
Thank you in advance,
Nov 06 2023 10:53 AM
In Excel, you cannot set a password to prompt for saving without going through a password-protected workbook, which also provides read-only access. However, you can implement a workaround using VBA to accomplish your goal. The idea is to use a macro to prompt for a password when you try to save the workbook, but allow free access for reading and editing.
Here is a step-by-step guide on how to set up such a system:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Password As String Password = InputBox("Enter the save password:") ' Replace "YourPassword" with your desired password If Password <> "YourPassword" Then MsgBox "Incorrect password. Changes will not be saved.", vbExclamation Cancel = True End If End Sub
5. Modify the code by replacing "YourPassword" with your actual desired save password.
6. Save the workbook as a macro-enabled workbook by selecting "File" > "Save As" and choosing the file type "Excel Macro-Enabled Workbook (*.xlsm)."
7. Close the VBA editor.
Now, whenever you try to save the workbook, Excel will prompt for the save password. If you enter the correct password, it will allow you to save. If you enter the incorrect password or click Cancel, the changes will not be saved.
Please be aware that this method relies on security through obscurity. Anyone with access to the VBA editor can view the password. So, it's not a foolproof method, but it can help prevent accidental saves.The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
Nov 07 2023 07:24 AM