Unprotect sheet button stops working

A

All2Cheesy

Board Regular
Joined
Mar 4, 2015
Messages
127
Hi all,

I've written some code which assigns a temporary password to excel and locks all cells in the workbook before reassigning the original password. This seems to work fine, however, I've noticed that once the code has been run the sheet can be unlocked by simply pressing the unprotect sheet button. Doing so does not prompt the user for a password, which is obviously quite a large flaw. Does anyone have an idea as to why this could be happening? Many thanks in advance!

Code: 
Sub ProtectAll()

'Disable additional features
With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .EnableEvents = False
        .DisplayAlerts = False
End With

'Unlock Sheet
'ActiveSheet.Unprotect Password:="origpass"

'Lock Sheet with new password
ActiveSheet.Protect Password:="temppass"



 Dim S As Object
    Dim pWord1 As String, pWord2 As String
    pWord1 = InputBox("Please Enter the password")
    If pWord1 = "" Then Exit Sub
    pWord2 = InputBox("Please re-enter the password")
     
    If pWord2 = "" Then Exit Sub
     'make certain passwords are identical
    If InStr(1, pWord2, pWord1, 0) = 0 Or _
    InStr(1, pWord1, pWord2, 0) = 0 Then
        MsgBox "You entered different passwords. No action taken"
        Exit Sub
    End If

    For Each Worksheet In Worksheets
        Worksheet.Protect Password:=pWord1, AllowFiltering:=True
    Next

Sheets("Sheet1").Protect UserInterFaceOnly:=True
Sheets("Sheet2").Protect UserInterFaceOnly:=True
Sheets("Sheet3").Protect UserInterFaceOnly:=True
Sheets("Sheet4").Protect UserInterFaceOnly:=True
Sheets("Sheet5").Protect UserInterFaceOnly:=True
Sheets("Sheet6").Protect UserInterFaceOnly:=True

Sheets("Sheet1").Range("A1:XFD1048576").Locked = True
Sheets("Sheet2").Range("A1:XFD1048576").Locked = True
Sheets("Sheet3").Range("A1:XFD1048576").Locked = True
Sheets("Sheet4").Range("A1:XFD1048576").Locked = True
Sheets("Sheet5").Range("A1:XFD1048576").Locked = True
Sheets("Sheet6").Range("A1:XFD1048576").Locked = True
    
    For Each Worksheet In Worksheets
        Worksheet.Protect Password:=origpass, AllowFiltering:=True
    Next

    
'Enable formatting
    Worksheets("sheet2").Protect , AllowFormattingRows:=True
 
 'Enable additional feautres
With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
End With
 

End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
What happens if you place these in the immediates window and press Enter
Then rerun the code

Code: 
  Application.ScreenUpdating = True
        Application.Calculation = xlAutomatic
        Application.EnableEvents = True
        Application.DisplayAlerts = True
 
Upvote 0
I just gave that a go. Excel requires me to enter the password in multiple times (once for each sheet), and the same issue as above still occurs.
 
Upvote 0
Are these 2 line always commented out ??
Code: 
'Unlock Sheet
'ActiveSheet.Unprotect Password:="origpass"
 
Upvote 0
The second line is not meant to be commented out... I had commented it out when testing the code and forgot to uncomment it when posting my code here. Terribly sorry for the confusion. :confused:
 
Upvote 0
The reason you can uprotect the sheets without a password is this:

Your variable: [ origpass ] is not declared.
So the variable is set to blank by default.

If you meant to declare your password as a variable, then now would be a good time.

Code: 
    origpass = "Mypassword"

    For Each Worksheet In Worksheets
        Worksheet.Protect Password:=origpass, AllowFiltering:=True
    Next

However, if you meant for your password to be origpass, then surround it with ""

Code: 
    For Each Worksheet In Worksheets
        Worksheet.Protect Password:="origpass", AllowFiltering:=True
    Next

If you would, it is always best to use Option Explicit at the very top of your VBA Module.
It is super handy and will warn you of these types of oversights before they become an issue.

With declaring Option Explicit, VBA will treat things it doesn't understand as variables.
Whether they are meant to be a variable or not.

Code: 
'Option Explicit Example: Declaring Option Explicit on the very top of the module sets it for the entire module:
Option Explicit

Sub MyPass ()

    origpass = "Mypassword"

    For Each Worksheet In Worksheets
        Worksheet.Protect Password:=origpass, AllowFiltering:=True
    Next

End Sub

Sub Num2 ()
  'Some Code
End Sub

Hope this helps.
 
Last edited:
Upvote 0
Now that I look a little closer, you are saving all your worksheets with a new password.
The tests were protecting and protecting manually and via Loops

Try this, it follows your code, but cleaned up some of the duplicate code causing you headaches:

Code: 
Sub ProtectAll()

'Disable additional features
With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .EnableEvents = False
        .DisplayAlerts = False
End With

'Unlock Sheet
'ActiveSheet.Unprotect Password:="origpass"

'Lock Sheet with new password
ActiveSheet.Protect Password:="temppass"



        Dim S As Object, Worksheet As Worksheet
        Dim pWord1 As String, pWord2 As String
    
    pWord1 = InputBox("Please Enter the password")
        If pWord1 = "" Then Exit Sub
        
    pWord2 = InputBox("Please re-enter the password")
        If pWord2 = "" Then
            MsgBox "Blank Passwords are not allowed, Press OK To Quit"
            Exit Sub
        End If
        
         'make certain passwords are identical
        If pWord2 <> pWord2 Then
            MsgBox "You entered different passwords. No action taken"
            Exit Sub
        End If

        Sheets("Sheet1").Protect UserInterFaceOnly:=True
        Sheets("Sheet2").Protect UserInterFaceOnly:=True
        Sheets("Sheet3").Protect UserInterFaceOnly:=True
        Sheets("Sheet4").Protect UserInterFaceOnly:=True
        Sheets("Sheet5").Protect UserInterFaceOnly:=True
        Sheets("Sheet6").Protect UserInterFaceOnly:=True
        
        Sheets("Sheet1").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet2").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet3").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet4").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet5").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet6").Range("A1:XFD1048576").Locked = True

    'Protect all Sheets
        For Each Worksheet In Worksheets
            Worksheet.Protect Password:=pWord2, AllowFiltering:=True
        Next
    
    'Enable formatting in Sheet 2
        Worksheets("sheet2").Unprotect Password:=pWord2
        Worksheets("sheet2").Protect Password:=pWord2, AllowFormattingRows:=True
 
 'Enable additional feautres
 
With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
End With
 

End Sub
 
Upvote 0
you could probably also use
Code: 
dim x as integer
For x = 1 To 6
        Sheets("Sheet" & x).Protect UserInterFaceOnly:=True
        Sheets("Sheet" & x).Range("A1:XFD1048576").Locked = True
Next x
instead of
Code: 
        Sheets("Sheet1").Protect UserInterFaceOnly:=True
        Sheets("Sheet2").Protect UserInterFaceOnly:=True
        Sheets("Sheet3").Protect UserInterFaceOnly:=True
        Sheets("Sheet4").Protect UserInterFaceOnly:=True
        Sheets("Sheet5").Protect UserInterFaceOnly:=True
        Sheets("Sheet6").Protect UserInterFaceOnly:=True
        
        Sheets("Sheet1").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet2").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet3").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet4").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet5").Range("A1:XFD1048576").Locked = True
        Sheets("Sheet6").Range("A1:XFD1048576").Locked = True
 
Upvote 0
Unfortunately not, the sheets each have different names, for the purpose of simplicity I changed the names of the worksheets to generic ones in this example.

I do appreciate the suggestion though. I will keep that in mind for any future excel projects. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

SaraWitch
  • Question
Unprotect/protect all worksheets with a checkbox
Replies
3
Views
591
skillilea
S
W
  • Question
multiple password protected pages with same password and using macros
Replies
0
Views
455
WindowGuy
W
M
  • Question
Filtering / Sorting a protected Sheet
Replies
1
Views
681
ExcelToDAX
E
F
  • Question
VBA - formatting/grouping with protected workbook
Replies
1
Views
465
ExcelToDAX
E
noisepoet
  • Solved
unprotect sheet via vba works first time, says incorrect password second time
Replies
3
Views
131
Fluff
Fluff

Forum statistics

Threads
1,214,832
Messages
6,121,843
Members
449,051
Latest member
excelquestion515

Share this page

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