prevent saving unless password

288enzo

Well-known Member
Joined
Feb 8, 2009
Messages
717
Office Version
  1. 2016
Platform
  1. Windows
Good morning,

I have a bit of code that will protect all my sheets on exit, and another bit of code that if you want to unlock all the sheets you have to enter a password.

Instead of having to protect all the sheets, could I instead prevent users from saving the file (protecting from accidental changes) using a password to save?

I'm running 2016 Pro.

Thank you
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
You can add your password request to this part of the macro :

VBA Code:
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True 'Cancels any request to save the file
    MsgBox "Saving is prohibited.", vbCritical, "Save Cancelled"
End Sub
 
Upvote 0
You can add your password request to this part of the macro :

VBA Code:
Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True 'Cancels any request to save the file
    MsgBox "Saving is prohibited.", vbCritical, "Save Cancelled"
End Sub
Thank you, but I'm not sure how I go about adding it.

I have this code that requires a password to unprotect the sheets.
VBA Code:
Sub UnprotectAllSheets()
Dim ws As Worksheet
Dim strPwd As String
Dim strCheck As String
strCheck = "XXXXXXXXXX"
strPwd = InputBox("Enter Password", "Password", "Enter Password")
If strPwd = strCheck Then
  For Each ws In ThisWorkbook.Worksheets
    ws.Unprotect Password:=strPwd
  Next ws
Else
  MsgBox "Incorrect Password"
End If
End Sub

And this is the code that locks it all up on exit.
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim xSheet As Worksheet
    Dim xPsw As String
    xPsw = "XXXXXXXXXX"
    For Each xSheet In Worksheets
        xSheet.Protect xPsw, AllowFiltering:=True
    Next
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top