Link to home
Start Free TrialLog in
Avatar of rltomalin
rltomalinFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
SOLUTION
Avatar of Runrigger
Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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
Avatar of rltomalin

ASKER


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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent thanks.
As all three gave the initial solution I have shared the points out.  I hope I have done it fairly.

Regards

Richard
Any award of points is appreciated, so thank you and happy to help.
Dave