INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Macro to Protect all sheets in an workbook and set protection parameters
2

Macro to Protect all sheets in an workbook and set protection parameters

Macro to Protect all sheets in an workbook and set protection parameters

(OP)
I am using Office 365 Excel 2016.

I am using Macro 1 to lock all sheets in the workbook. It pops up a message box so I can enter a password, verifies the password, and protects all the sheets. If the passwords do not match, it starts over.

I would like to use this macro in a workbook where I am using filtering. In thread 68-806201 I found Macro 2 which will Protect a single sheet and allow filtering. The problem is it uses a hard coded password and it only does one sheet.

Question- How do I combine Macro 2 into Macro 1?
Thank you for your Help


Macro 1

CODE -->

Sub protect_all_sheets()
top:
pass = InputBox("Enter Password?")
repass = InputBox("Verify Password")
If Not (pass = repass) Then
MsgBox "Your Passwords do not Match"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each Sheet In ActiveWorkbook.Worksheets
Sheet.Protect Password:=pass
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please un-protect all sheets then running this Macro."
End Sub 



Macro 2

CODE -->

ActiveSheet.Protect "pass", True, True, _
True , , , , , , , , , , True, True

ActiveSheet.EnableSelection = 1 

RE: Macro to Protect all sheets in an workbook and set protection parameters

Hi,

CODE

Sub protect_all_sheets()
    Dim pass As String, repass As String
    Dim i As Integer, sheet As Worksheet
    
    pass = InputBox("Enter Password?")
    repass = InputBox("Verify Password")
    
    If Not (pass = repass) Then
        MsgBox "Your Passwords do not Match"
        pass = InputBox("Enter Password?")
        repass = InputBox("Verify Password")
    End If
    
    For i = 1 To Worksheets.Count
        If Worksheets(i).ProtectContents Then
            MsgBox "I think you have some sheets that are already protected. " & _
                 "Please un-protect all sheets then running this Macro."
            Exit sub
        End If
    Next
    
    For Each sheet In ActiveWorkbook.Worksheets
        sheet.Protect pass, True, True, _
            True, , , , , , , , , , True, True

        sheet.EnableSelection = 1
    Next
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro to Protect all sheets in an workbook and set protection parameters

For years I have used the following pair of macros to protect & unprotect workbooks.  I use them so much I converted them to an add-in, and have them able to be activated via Ctrl-Shift-P and Crtl-Shift-U shortcuts.

I see two potential, but minor, improvements in this thread's earlier offerings:
» Requiring double entry of the password before protecting;
» Checking whether anything is already protected before attempting my own protection.

CODE

Option Explicit
Option Base 1
Sub Protect_All()
'
'  Macro to apply a hardwired password to all sheets in a workbook and to the workbook itself.
'
Dim WorkSht As Worksheet, ThisChart As Chart, NumbSheets As Long, NumbCharts As Long
Dim PassWd, Ans, ShtName As String, ShtType As String
Const Descr As String = "Macro to protect all worksheets etc"
'
'  Warn user what is about to happen.
'
Ans = MsgBox("You are about to protect all sheets & charts in this workbook." & _
                  Chr(13) & Chr(13) & "Do you wish to continue?", _
                  vbYesNoCancel + vbDefaultButton1, Descr)
If Ans = vbCancel Or Ans = vbNo Then
    MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
    Exit Sub
End If
'
'  Get from the user the password to be used.
'
'  Note that the "Application." in front of the "InputBox" for the latter is necessary
'  to be able to distinguish between a blank password and a "cancel" response, since with it
'  a cancel will return a boolean "false", while without it a cancel will return an empty
'  string.
'
PassWd = Application.InputBox("Please enter the password you want to use:", Descr)
If VarType(PassWd) = vbBoolean Then
    If Not PassWd Then
        MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
        Exit Sub
    End If
End If
'
'  Loop through all the worksheets.
'
NumbSheets = 0
ShtType = "Worksheet "
For Each WorkSht In Worksheets
    ShtName = WorkSht.Name
    On Error GoTo P_Failure
    '
    WorkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=PassWd
    '
    '  Allow user to select (but not change) locked cells.  Note that with some versions
    '  of Excel this setting does not persist (ie it gets forgotten when the workbook
    '  is saved.
    '
    WorkSht.EnableSelection = xlNoRestrictions
    '
    On Error GoTo 0
    NumbSheets = NumbSheets + 1
Next WorkSht
'
'  Loop through all the charts.
'
NumbCharts = 0
ShtType = "Chart "
For Each ThisChart In Charts
    ShtName = ThisChart.Name
    On Error GoTo P_Failure
    ThisChart.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=PassWd
    On Error GoTo 0
    NumbCharts = NumbCharts + 1
Next ThisChart
'
'  Now protect the workbook itself.
'
ShtType = ""
ShtName = "Workbook's structure"
On Error GoTo P_Failure
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=PassWd
On Error GoTo 0
'
'  It's all over.
'
MsgBox "All done OK  (" & NumbSheets & " sheets and " & NumbCharts & " charts)." & Chr(13) & Chr(13) & _
       "Password used was """ & PassWd & """." & Chr(13) & Chr(13) & _
       "Take care not to forget it.", vbOKOnly, Descr
Exit Sub
'
'  Error handling area.
'
P_Failure:
MsgBox "Protection attempt failed for " & ShtType & ShtName & " so exercise was aborted." & _
       Chr(13) & Chr(13) & _
       Err & ": " & Error(Err), _
       vbOKOnly, Descr
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Unprotect_All()
'
'  Macro to unprotect all sheets in a workbook, and the workbook itself.
'  It assumes that all these protections have been set with the same password.
'
Dim WorkSht As Worksheet, ThisChart As Chart, NumbSheets As Long, NumbCharts As Long
Dim Ans, PassWd, ShtName As String, ShtType As String
Const Descr As String = "Macro to unprotect all worksheets etc"
'
'  Warn user what is about to happen.
'
Ans = MsgBox("You are about to unprotect all sheets & charts in this workbook." & _
                  Chr(13) & Chr(13) & "Do you wish to continue?", _
                  vbYesNoCancel + vbDefaultButton1, Descr)
If Ans = vbCancel Or Ans = vbNo Then
    MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
    Exit Sub
End If
'
'  Get the password from the user.
'  (See above for comments on the "Application." bit.)
'
PassWd = Application.InputBox("Please enter the password:", Descr)
If VarType(PassWd) = vbBoolean Then
    If Not PassWd Then
        MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
        Exit Sub
    End If
End If
'
'  Loop through all the worksheets.
'
NumbSheets = 0
ShtType = "Worksheet "
For Each WorkSht In Worksheets
    ShtName = WorkSht.Name
    On Error GoTo U_Failure
    WorkSht.Unprotect Password:=PassWd
    On Error GoTo 0
    NumbSheets = NumbSheets + 1
Next WorkSht
'
'  Loop through all the charts.
'
NumbCharts = 0
ShtType = "Chart "
For Each ThisChart In Charts
    ShtName = ThisChart.Name
    On Error GoTo U_Failure
    ThisChart.Unprotect Password:=PassWd
    On Error GoTo 0
    NumbCharts = NumbCharts + 1
Next ThisChart
'
'  Now unprotect the workbook itself.
'
ShtType = ""
ShtName = "Workbook's structure"
On Error GoTo U_Failure
ActiveWorkbook.Unprotect Password:=PassWd
On Error GoTo 0
'
'  It's all over.
'
MsgBox "All done OK  (" & NumbSheets & " sheets and " & NumbCharts & " charts).", vbOKOnly, Descr
Exit Sub
'
'  Error handling area.
'
U_Failure:
MsgBox "Unprotection attempt failed for " & ShtType & ShtName & " so exercise was aborted." & _
       Chr(13) & Chr(13) & _
       Err & ": " & Error(Err), _
       vbOKOnly, Descr
End Sub 

RE: Macro to Protect all sheets in an workbook and set protection parameters

(OP)
Hi Skip I tested you Macro and it works Perfectly.

Deniall- Thank you for your response- I tried your macro as well and it works very well except it is not allowing me to filter. Your code is way above my abilities to adjust. Can you provide further help?


Update- Deniall I figured out how to allow for the filtering, Thank you for your help. However in your comments before the code you mention a double entry of the password and checking to see if all sheets are unprotected. However when I run your code it does not confirm the password or check for protected sheets. Am I doing something wrong?

RE: Macro to Protect all sheets in an workbook and set protection parameters

You are mis-reading my second paragraph, which I now see is potentially ambiguous.  What I was trying to say is that my macros do NOT have these features, and would be improved by having them included.  (I may well get around to doing just that, but not right now.)

RE: Macro to Protect all sheets in an workbook and set protection parameters

(OP)
Hello Deniall,

I was testing your macro and I was able to change the code to require double entry of the password and check for locked sheets. If you do not check for locked sheets your spreadsheet chas the potential to have 2 passwords.

CODE -->

Option Explicit
Option Base 1
Sub Protect_All()
'
'  Macro to apply a hardwired password to all sheets in a workbook and to the workbook itself.
'
Dim WorkSht As Worksheet, ThisChart As Chart, NumbSheets As Long, NumbCharts As Long
Dim PassWd, RePassWd, Ans, ShtName As String, ShtType As String, i As Integer
Const Descr As String = "Macro to protect all worksheets etc"
'
'  Warn user what is about to happen.
'
Ans = MsgBox("You are about to protect all sheets & charts in this workbook." & _
                  Chr(13) & Chr(13) & "Do you wish to continue?", _
                  vbYesNoCancel + vbDefaultButton1, Descr)
If Ans = vbCancel Or Ans = vbNo Then
    MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
    Exit Sub
End If
'
'  Get from the user the password to be used and Verify the the password with a second entry
'  of the password. If they do not match loop through the entry process.
'
'  Note that the "Application." in front of the "InputBox" for the latter is necessary
'  to be able to distinguish between a blank password and a "cancel" response, since with it
'  a cancel will return a boolean "false", while without it a cancel will return an empty
'  string.
'
PassWd = Application.InputBox("Please enter the password you want to use:", Descr)
RePassWd = Application.InputBox("Please Re-enter the password:", Descr)
If Not (PassWd = RePassWd) Then
        MsgBox "Your Passwords do not Match"
        PassWd = InputBox("Enter Password?")
        RePassWd = InputBox("Verify Password")
    End If
'
'  This Section Checks to see if any of the sheets are currently Protected.
'  If it finds a protected sheet it stops the entry of an additional password
'
For i = 1 To Worksheets.Count
        If Worksheets(i).ProtectContents Then
            MsgBox "I think you have some sheets that are already protected. " & _
                 "Please un-protect all sheets then running this Macro."
            Exit Sub
        End If
    Next
    
If VarType(PassWd) = vbBoolean Then
    If Not PassWd Then
        MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
        Exit Sub
    End If
End If
'
'  Loop through all the worksheets.
'
NumbSheets = 0
ShtType = "Worksheet "
For Each WorkSht In Worksheets
    ShtName = WorkSht.Name
    On Error GoTo P_Failure
    '
    WorkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True, Password:=PassWd
    '
    '  Allow user to select (but not change) locked cells.  Note that with some versions
    '  of Excel this setting does not persist (ie it gets forgotten when the workbook
    '  is saved.
    '
    WorkSht.EnableSelection = xlNoRestrictions
    '
    On Error GoTo 0
    NumbSheets = NumbSheets + 1
Next WorkSht
'
'  Loop through all the charts.
'
NumbCharts = 0
ShtType = "Chart "
For Each ThisChart In Charts
    ShtName = ThisChart.Name
    On Error GoTo P_Failure
    ThisChart.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=PassWd
    On Error GoTo 0
    NumbCharts = NumbCharts + 1
Next ThisChart
'
'  Now protect the workbook itself.
'
ShtType = ""
ShtName = "Workbook's structure"
On Error GoTo P_Failure
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=PassWd
On Error GoTo 0
'
'  It's all over.
'
MsgBox "All done OK  (" & NumbSheets & " sheets and " & NumbCharts & " charts)." & Chr(13) & Chr(13) & _
       "Password used was """ & PassWd & """." & Chr(13) & Chr(13) & _
       "Take care not to forget it.", vbOKOnly, Descr
Exit Sub
'
'  Error handling area.
'
P_Failure:
MsgBox "Protection attempt failed for " & ShtType & ShtName & " so exercise was aborted." & _
       Chr(13) & Chr(13) & _
       Err & ": " & Error(Err), _
       vbOKOnly, Descr
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Unprotect_All()
'
'  Macro to unprotect all sheets in a workbook, and the workbook itself.
'  It assumes that all these protections have been set with the same password.
'
Dim WorkSht As Worksheet, ThisChart As Chart, NumbSheets As Long, NumbCharts As Long
Dim Ans, PassWd, ShtName As String, ShtType As String
Const Descr As String = "Macro to unprotect all worksheets etc"
'
'  Warn user what is about to happen.
'
Ans = MsgBox("You are about to unprotect all sheets & charts in this workbook." & _
                  Chr(13) & Chr(13) & "Do you wish to continue?", _
                  vbYesNoCancel + vbDefaultButton1, Descr)
If Ans = vbCancel Or Ans = vbNo Then
    MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
    Exit Sub
End If
'
'  Get the password from the user.
'  (See above for comments on the "Application." bit.)
'
PassWd = Application.InputBox("Please enter the password:", Descr)
If VarType(PassWd) = vbBoolean Then
    If Not PassWd Then
        MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
        Exit Sub
    End If
End If
'
'  Loop through all the worksheets.
'
NumbSheets = 0
ShtType = "Worksheet "
For Each WorkSht In Worksheets
    ShtName = WorkSht.Name
    On Error GoTo U_Failure
    WorkSht.Unprotect Password:=PassWd
    On Error GoTo 0
    NumbSheets = NumbSheets + 1
Next WorkSht
'
'  Loop through all the charts.
'
NumbCharts = 0
ShtType = "Chart "
For Each ThisChart In Charts
    ShtName = ThisChart.Name
    On Error GoTo U_Failure
    ThisChart.Unprotect Password:=PassWd
    On Error GoTo 0
    NumbCharts = NumbCharts + 1
Next ThisChart
'
'  Now unprotect the workbook itself.
'
ShtType = ""
ShtName = "Workbook's structure"
On Error GoTo U_Failure
ActiveWorkbook.Unprotect Password:=PassWd
On Error GoTo 0
'
'  It's all over.
'
MsgBox "All done OK  (" & NumbSheets & " sheets and " & NumbCharts & " charts).", vbOKOnly, Descr
Exit Sub
'
'  Error handling area.
'
U_Failure:
MsgBox "Unprotection attempt failed for " & ShtType & ShtName & " so exercise was aborted." & _
       Chr(13) & Chr(13) & _
       Err & ": " & Error(Err), _
       vbOKOnly, Descr
End Sub 

RE: Macro to Protect all sheets in an workbook and set protection parameters

(OP)
Skip

You get a big thank you as I was able to look at your code and improve Deniall's code. I also like the efficiency of your code unfortunately I have not idea what I am doing so my changes were guesses that worked out well.

Thank you for your help.

Michael

RE: Macro to Protect all sheets in an workbook and set protection parameters

Great. Just keep coding and coming back to Tek-Tips. Its been a huge help for me. I’ve learned a bunch and I’m still learning from these guys.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro to Protect all sheets in an workbook and set protection parameters

MJV

Well done.  Your modifications work OK, except that they do not cover Chart Sheets.  I have remedied this in what I present below, and at the same time I have (I hope) made thing a little bit more robust.  Thanks for encouraging me on this little improvement project.

CODE

Option Explicit
Option Base 1

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Protect_All()
'
'  Macro to apply a (single) password to all sheets in a workbook and to the workbook itself.
'
Dim WorkSht As Worksheet, ThisChart As Chart, NumbSheets As Long, NumbCharts As Long
Dim PassWd, RePassWd, Ans, ShtName As String, ShtType As String, I As Integer
Const Descr As String = "Macro to fully protect a workbook"
'
'  Warn user what is about to happen.
'
Ans = MsgBox("You are about to protect all sheets & charts in this workbook." & _
                  Chr(13) & Chr(13) & "Do you wish to continue?", _
                  vbYesNoCancel + vbDefaultButton1, Descr)
If Ans = vbCancel Or Ans = vbNo Then
    MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
    Exit Sub
End If
'
'  Get from the user the password to be used and Verify the the password with a second entry
'  of the password. If they do not match repeat the entry process (up to 3 times).
'
'  Note that the "Application." in front of the "InputBox" for the latter is necessary
'  to be able to distinguish between a blank password and a "cancel" response, since with it
'  a cancel will return a boolean "false", while without it a cancel will return an empty
'  string.
'
For I = 1 To 3
    PassWd = Application.InputBox("Please enter the password you want to use:", Descr)
    If VarType(PassWd) = vbBoolean Then
        If Not PassWd Then
            MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
            Exit Sub
        End If
    End If
    '
    RePassWd = Application.InputBox("Please re-enter the password:", Descr)
    If VarType(RePassWd) = vbBoolean Then
        If Not RePassWd Then
            MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
            Exit Sub
        End If
    End If
    '
    If PassWd = RePassWd Then
        Exit For
    Else
        If I < 3 Then
            MsgBox "Your passwords do not match.  Try again.", , Descr
            GoTo Try_Again
        Else
            MsgBox "Another mis-match.  Three strikes and you're out.", vbOKOnly, Descr
            Exit Sub
        End If
    End If
Try_Again:
Next I
'
'  Check to see if any of the worksheets or chartsheets are currently protected.
'  If we find a protected sheet we abort the entire process.
'
For Each WorkSht In Worksheets
    If WorkSht.ProtectContents Then
        MsgBox "You appear to have some sheets that are already protected. " & Chr(13) & _
               "Please un-protect all sheets/charts before running this macro."
        Exit Sub
    End If
Next WorkSht
'
For Each ThisChart In Charts
    If ThisChart.ProtectContents Then
        MsgBox "You appear to have some charts that are already protected. " & Chr(13) & _
               "Please un-protect all sheets/charts before running this macro."
        Exit Sub
    End If
Next ThisChart
'
'  Checks all passed.  Can now get on with the main job.
'
'  Loop through all the worksheets.
'
NumbSheets = 0
ShtType = "Worksheet "
For Each WorkSht In Worksheets
    ShtName = WorkSht.Name
    On Error GoTo P_Failure
    '
    WorkSht.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True, Password:=PassWd
    '
    '  Allow user to select (but not change) locked cells.  Note that with some versions
    '  of Excel this setting does not persist (ie it gets forgotten when the workbook
    '  is saved.
    '
    WorkSht.EnableSelection = xlNoRestrictions
    '
    On Error GoTo 0
    NumbSheets = NumbSheets + 1
Next WorkSht
'
'  Loop through all the charts.
'
NumbCharts = 0
ShtType = "Chart "
For Each ThisChart In Charts
    ShtName = ThisChart.Name
    On Error GoTo P_Failure
    ThisChart.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=PassWd
    On Error GoTo 0
    NumbCharts = NumbCharts + 1
Next ThisChart
'
'  Now protect the workbook itself.
'
ShtType = ""
ShtName = "Workbook's structure"
On Error GoTo P_Failure
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=PassWd
On Error GoTo 0
'
'  It's all over.
'
MsgBox "All done OK  (" & NumbSheets & " sheets and " & NumbCharts & " charts)." & Chr(13) & Chr(13) & _
       "Password used was """ & PassWd & """." & Chr(13) & Chr(13) & _
       "Take care not to forget it.", vbOKOnly, Descr
Exit Sub
'
'  Error handling area.
'
P_Failure:
MsgBox "Protection attempt failed for " & ShtType & ShtName & " so exercise was aborted." & _
       Chr(13) & Chr(13) & _
       Err & ": " & Error(Err), _
       vbOKOnly, Descr
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Unprotect_All()
'
'  Macro to unprotect all sheets in a workbook, and the workbook itself.
'  It assumes that all these protections have been set with the same password.
'
Dim WorkSht As Worksheet, ThisChart As Chart, NumbSheets As Long, NumbCharts As Long
Dim Ans, PassWd, ShtName As String, ShtType As String
Const Descr As String = "Macro to fully unprotect a workbook"
'
'  Warn user what is about to happen.
'
Ans = MsgBox("You are about to unprotect all sheets & charts in this workbook." & _
                  Chr(13) & Chr(13) & "Do you wish to continue?", _
                  vbYesNoCancel + vbDefaultButton1, Descr)
If Ans = vbCancel Or Ans = vbNo Then
    MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
    Exit Sub
End If
'
'  Get the password from the user.
'  (See above for comments on the "Application." bit.)
'
PassWd = Application.InputBox("Please enter the password:", Descr)
If VarType(PassWd) = vbBoolean Then
    If Not PassWd Then
        MsgBox "Operation cancelled at your request.", vbOKOnly, Descr
        Exit Sub
    End If
End If
'
'  Loop through all the worksheets.
'
NumbSheets = 0
ShtType = "Worksheet "
For Each WorkSht In Worksheets
    ShtName = WorkSht.Name
    On Error GoTo U_Failure
    WorkSht.Unprotect Password:=PassWd
    On Error GoTo 0
    NumbSheets = NumbSheets + 1
Next WorkSht
'
'  Loop through all the charts.
'
NumbCharts = 0
ShtType = "Chart "
For Each ThisChart In Charts
    ShtName = ThisChart.Name
    On Error GoTo U_Failure
    ThisChart.Unprotect Password:=PassWd
    On Error GoTo 0
    NumbCharts = NumbCharts + 1
Next ThisChart
'
'  Now unprotect the workbook itself.
'
ShtType = ""
ShtName = "Workbook's structure"
On Error GoTo U_Failure
ActiveWorkbook.Unprotect Password:=PassWd
On Error GoTo 0
'
'  It's all over.
'
MsgBox "All done OK  (" & NumbSheets & " sheets and " & NumbCharts & " charts).", vbOKOnly, Descr
Exit Sub
'
'  Error handling area.
'
U_Failure:
MsgBox "Unprotection attempt failed for " & ShtType & ShtName & " so exercise was aborted." & _
       Chr(13) & Chr(13) & _
       Err & ": " & Error(Err), _
       vbOKOnly, Descr
End Sub 

RE: Macro to Protect all sheets in an workbook and set protection parameters

(OP)
Hi Deniall,

Thank you for the improvement! My work was a copy of the work you and Skip created. To be honest I don't know why I needed to do what I did but I read the errors and copied you work..

It has been a pleasure to work with the both of you.

Michael

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close