Protect/Unprotect selective worksheets with Command Button and Password

[rachelkirk]

I am using Excel 2010 and would like to have the following code to protect and unprotect all worksheets in a command button with a password which is only required to unprotect the sheets. However, I am a novice and the code doesn't seem to work. Would you take a look at it?

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
If CommandButton1.Caption = "Protect" Then
    For Each sh In ThisWorkbook.Sheets
        sh.Protect
    Next sh
    CommandButton1.Caption = "Unprotect"
Else
    For Each sh In ThisWorkbook.Sheets
        sh.Unprotect Password:="2013"
        InputBox ("Please type password")
        
    Next sh
    CommandButton1.Caption = "Protect"
End If
Application.ScreenUpdating = True
End Sub

Besides, if I would like to protect only a number of the worksheets instead of the whole workbook from editing, how should the code be changed? Thanks.

 

[ArthriticPanda]

Hi rachelkirk,

If you make the following changes it should work ok.

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

If CommandButton1.Caption = "Protect" Then
    For Each sh In ThisWorkbook.Sheets
        sh.Protect
    Next sh
        CommandButton1.Caption = "Unprotect"
Else
    ans = InputBox("Please type password")
        If ans = "2013" Then
            For Each sh In ThisWorkbook.Sheets
                sh.Unprotect
            Next sh
        End If
    CommandButton1.Caption = "Protect"
End If
Application.ScreenUpdating = True
End Sub

To be more selective with your sheets you will have to build in a bit more code to identify the sheet you want to unlock. You could probably do this with a couple of input boxes...one to ask the password and the other to unlock the named sheet.

Hope this helps,

AP

 

[rachelkirk]

Hi AP,

Thanks for your suggestion. I tried it but it showed a Debug error 1004 highlighting the "sh.Unprotect" line. Any idea?

RK

 

[ArthriticPanda]

Hi Rachel,

I populated it 2007 and it seems to work ok. I Can't replicate your debug error.

Try declaring your sh. variable at the start of your code e.g.

dim sh as worksheet

AP