On 26 Mar, 09:43, Mike H wrote:
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



"Sarah (OGI)" wrote:
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.- Hide quoted text -

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