Macro to prompt for password to unprotect worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sarah (OGI) - 26 Mar 2008 10:29 GMT

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.

Reply to this Message

Mike H - 26 Mar 2008 10:43 GMT

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

> I've got the following vb code in an Excel macro to unprotect all worksheets
> in a document:
[quoted text clipped - 19 lines]
>
> Many thanks in advance.

Reply to this Message

Gmspences10@googlemail.com - 26 Mar 2008 10:58 GMT

> Sarah,
>
[quoted text clipped - 56 lines]
>
> - 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.

Reply to this Message