?
Solved

Excel unprotect workbook with single password

Posted on 2011-05-10
7
Medium Priority
?
551 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:rltomalin
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 600 total points
ID: 35727085

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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 600 total points
ID: 35727092
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35727095

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:rltomalin
ID: 35727319

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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 800 total points
ID: 35727324
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
 

Author Closing Comment

by:rltomalin
ID: 35727482
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
 
LVL 11

Expert Comment

by:Runrigger
ID: 35727633
Any award of points is appreciated, so thank you and happy to help.
Dave
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question