Excel unprotect workbook with single password

I have a macro to set the protection of each sheet in a workbook - as below:
-------------------------------------------------------------------------------------
Sub Unprotect_whole_workbook()
'
' Unprotect_whole_workbook Macro
'
For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:="password"
    Next ws
'
End Sub
----------------------------------------------------------------------

This works just fine.  But I would now like to change this macro so that the user has to enter the password.
But if I remove the password parameter from the above code, the macro of course requests the password for each sheet.
Could someone suggest the code necessary to unprotect the first sheet and then apply that password to all the other sheets.

Best regards

Richard Tomalin
rltomalinAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Sub Unprotect_whole_workbook()

' Unprotect_whole_workbook Macro

    Dim Password As String
   
    Password = InputBox("Enter password:")
    If Len(Password) = 0 Then
        MsgBox "No password entered. Workbook is protected."
        Exit Sub
    End If

    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next
        ws.Unprotect Password:=Password
        If Err.Number <> 0 Then
            MsgBox "Invalid password."
            Exit Sub
        End If
    Next ws

End Sub

Kevin
0
 
RunriggerConnect With a Mentor Commented:

Sub Unprotect_whole_workbook()
Dim sPassword as string
'
' Unprotect_whole_workbook Macro
'
sPassword = InputBox("Enter Password",vbquestion+vbokonly)


For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:=sPassword
    Next ws
'
End Sub
0
 
Rory ArchibaldConnect With a Mentor Commented:
Sub Unprotect_whole_workbook()
'
' Unprotect_whole_workbook Macro
'
Dim strPass as String
strPass = Inputbox("Enter password please")
For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:=strPass
    Next ws
'
End Sub

Open in new window


should do it.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
zorvek (Kevin Jones)ConsultantCommented:

Sub Unprotect_whole_workbook()

' Unprotect_whole_workbook Macro

    Dim Password As String
   
    Password = InputBox("Enter password:")
    If Len(Password) = 0 Then
        MsgBox "No password entered. Workbook is protected."
        Exit Sub
    End If

    For Each ws In ActiveWorkbook.Worksheets
        ws.Unprotect Password:=Password
    Next ws

End Sub

Kevin
0
 
rltomalinAuthor Commented:

Thanks all three of you for your response - it's all basically the same solution that puts me on the right track.

However, there is a slight problem.  Using the above code, if an incorrect password is entered, the VB code errors and the code error window (ie end, debug buttons etc).

Is there a way that I can trap that and just display the standard Excel message that just has an OK button to end the dialogue and keep the sheet protected?

Hope you follow that.

Thanks in anticipation.

Richard Tomalin
0
 
rltomalinAuthor Commented:
Excellent thanks.
As all three gave the initial solution I have shared the points out.  I hope I have done it fairly.

Regards

Richard
0
 
RunriggerCommented:
Any award of points is appreciated, so thank you and happy to help.
Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.