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.