Forum Discussion

camille425's avatar
camille425
Copper Contributor
Nov 06, 2023

Password in Excel only for Save

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, 

Camille

 

 

Excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 06, 2023

    camille425 

    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:

    1. Open your Excel workbook.
    2. Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
    3. In the VBA editor, right-click on the workbook name in the Project Explorer window and select "Insert" > "Module" to insert a new module.
    4. Copy and paste the following VBA code into the module:

    Vba code:

     

    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.

    • camille425's avatar
      camille425
      Copper Contributor
      Nov 07, 2023
      Hi NikolinoDE,
      Thank you for your nice answer.

      I try your VBA code and it is not working : I well open the document with "Activate the content" option for Macro.
      I opened the VBA editor, copy paste you code, replace the password :

      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      Dim Password As String
      Password = InputBox("Enter the save password:")
      If Password <> "12345" Then
      MsgBox "Incorrect password. Changes will not be saved.", vbExclamation
      Cancel = True
      End If
      End Sub

      Close the VBA editor, save the file macro enable file.
      When I open again the document, I try to modify something and when I save the file, it didn't open a box where we can put the password (here 12345 as an example).
      As I am not very good at VBA, could you please help ?

      Thank you again,
      Camille

Resources