Protecting and unprotecting a sheet

manguy

New Member
Joined
Feb 22, 2020
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
In a workbook I have sheet 1 fully protected with a password. On this sheet I have a button which when clicked prompts a user for the password. When the correct password is entered it takes the user to sheet 2 (unprotected) and re-protects sheet 1 as sheet 1 needs to always stay protected. This works fine providing the correct password is entered, however if an incorrect password is entered a run time error is displayed. What I need it to do is just display the usual message box advising that the entered password is incorrect. The vba code I'm using is below:

Sub protect()
'
' Protect Macro
'
ActiveSheet.Unprotect
Sheets("Sheet2").Select
Sheets("Sheet1").protect Password:="test"

End Sub

I'm very much a novice with vba code so if anyone can advise me how to correct this I would greatly appreciate it.

Thanks
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
Firstly, welcome to Mr. Excel!

Secondly, we need to see the code assigned to the button, too.

Thirdly, it's unclear as to the point of the button, when you wish sheet1 to be always protected, and all the button does, is to take the user to sheet2.
I'm sure all will be revealed, when we see the rest of your code...
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,026
Office Version
  1. 2019
Platform
  1. Windows
See if this helps,
VBA Code:
Sub remove_protection()
On Error Resume Next
    Sheets("Sheet1").Unprotect
If Err.Number = 1004 Or Sheets("Sheet1").ProtectContents = True Then MsgBox "Incorrect Password", vbCritical: Exit Sub
On Error GoTo 0
    Sheets("Sheet2").Select
    Sheets("Sheet1").Protect "test"
End Sub
This will exit when an incorrect password is entered or when the password input box is cancelled.
 

manguy

New Member
Joined
Feb 22, 2020
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
Thank you for replying and welcoming me to the forum, I did include the code in my post, it is only a small piece but here it is again:

Sub protect()
'
' Protect Macro
'
ActiveSheet.Unprotect
Sheets("Sheet2").Select
Sheets("Sheet1").protect Password:="test"

End Sub

Without going into too much detail about the point of the button and all that is does it is go to sheet 2, it is because sheet 1 will display results of an assessment which need to be locked. That may not make too much sense but please trust me, this is what I need.
 

manguy

New Member
Joined
Feb 22, 2020
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
See if this helps,
VBA Code:
Sub remove_protection()
On Error Resume Next
    Sheets("Sheet1").Unprotect
If Err.Number = 1004 Or Sheets("Sheet1").ProtectContents = True Then MsgBox "Incorrect Password", vbCritical: Exit Sub
On Error GoTo 0
    Sheets("Sheet2").Select
    Sheets("Sheet1").Protect "test"
End Sub
This will exit when an incorrect password is entered or when the password input box is cancelled.

jasonb75, That works perfectly, thank you so much. Sorry if I haven't really explained why I need this and that it doesn't appear to do that much, but trust me this is just what I need. Thank you again.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,422
Messages
5,547,834
Members
410,813
Latest member
Vhinzvirgo
Top