Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Dim myPwd As String
Application.ScreenUpdating = False
Set ws = Worksheets(1)
On Error Resume Next
Do
myPwd = GetPassword
ws.Unprotect Password:=myPwd
If myPwd < "" Then

If ws.ProtectContents Then

MsgBox "Invalid password, try again", vbOKOnly +
vbInformation, "Password input"
End If
End If
Loop Until Not ws.ProtectContents Or myPwd = ""
On Error GoTo 0
If myPwd < "" Then

For Each ws In ActiveWorkbook.Worksheets

If ws.ProtectContents = True Then

ws.Unprotect Password:=myPwd
End If
Next ws
ActiveWorkbook.Unprotect Password:=myPwd
Application.ScreenUpdating = True
End If
End Sub

Private Function GetPassword() As Variant
GetPassword = InputBox(Prompt:="Please enter the common password")
End Function

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tanya" wrote in message
...
Thank you Gary. I am glad you cleared that up.

Is there any way to all the user to try again if they get the password
wrong? because it comes up with a run-time error if the user puts the
wrong
password in..

Kind Regards
Tanya

"Gary Keramidas" wrote:

tanya:

dave just had a typo.

try this

Dim myPwd As String

--


Gary


"Tanya" wrote in message
...
Hi Dave
Thank you for your quick response to my problem.

I ran the code as you have it and got a 'compile error' on the
line -----
--------dim myPwd as Password

My second question is "do I replace 'myPwd' with the password I want to
use?
or do I need another line?

cheers
Tanya

"Dave Peterson" wrote:

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
dim myPwd as Password
Application.ScreenUpdating = False
mypwd = inputbox(Prompt:="Please enter the common password")
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect password:=mypwd
End If
Next ws
ActiveWorkbook.Unprotect password:=mypwd
Application.ScreenUpdating = True
End Sub

Since you're not changing selections, I bet you could drop both the
application.screenupdating lines.

Tanya wrote:

Hi I have the following code and want to prompt the user for the
correct
password. Can't seem to get it right. Can anyone help me?

Thanks in advance.

Tanya

Sub UnProtect_Workbook()
'Unprotect workbook
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.ProtectContents = True Then
ws.Unprotect ("BBHS")
End If
Next
ActiveWorkbook.Unprotect (["BBHS"])
Application.ScreenUpdating = True

End Sub

--

Dave Peterson