Macro to prompt for password to unprotect worksheets

[Sarah (OGI)]

I've got the following vb code in an Excel macro to unprotect all worksheets
in a document:

Sub UnprotectAllSheets()

Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Worksheets.Count
Worksheets(n).Unprotect Password:="password"
Next n
Application.ScreenUpdating = True

End Sub

The code is attached to a command button on the first worksheet. When the
button is selected, I'd like a prompt to appear to ask the user to input the
relevant password. If the password entered does not match what has been
specified in the code, the sheets cannot be unprotected. How can I do this?

Also, is there any way of disabling the sheet protection option on the menu
bar: Tools\Protection\Unprotect Sheet?

Many thanks in advance.

 

[Mike H]

Sarah,

A sumple way would be this
Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Worksheets.Count
response = InputBox("Enter password for sheet " & n)
On Error Resume Next
Worksheets(n).Unprotect Password:=response
Next n
Application.ScreenUpdating = True
End Sub

For your second question you need 2 bits of code. One in the workbook open
event and one in the before close event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application.CommandBars("Tools")
With .Controls("&Protection")
.Controls("&Unprotect Sheet...").Enabled = True
End With
End With
End Sub

Private Sub Workbook_Open()
With Application.CommandBars("Tools")
With .Controls("&Protection")
.Controls("&Unprotect Sheet...").Enabled = False
End With
End With
End Sub


Mike

 

[Gmspences10]

Sarah,

A sumple way would be this
Sub UnprotectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Worksheets.Count
    response = InputBox("Enter password for sheet " & n)
    On Error Resume Next
    Worksheets(n).Unprotect Password:=response
Next n
Application.ScreenUpdating = True
End Sub

For your second question you need 2 bits of code. One in the workbook open
event and one in the before close event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application.CommandBars("Tools")
        With .Controls("&Protection")
            .Controls("&Unprotect Sheet...").Enabled = True
        End With
    End With
End Sub

Private Sub Workbook_Open()
With Application.CommandBars("Tools")
        With .Controls("&Protection")
            .Controls("&Unprotect Sheet...").Enabled = False
        End With
    End With
End Sub

Mike











- Show quoted text -

Hi Sarah,

Alternately what you can do is have a UserForm on to prompt for the
password, and then in the properties of the text box you can change
the PasswordChar to *

With this approach you will also be able to have usernames with
specific passwords to open specific parts of the document.