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!
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