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