Want to protect & unprotect multiple sheets except 3 with vba

xaiez

New Member
Joined
May 5, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Sir,
Thanks in advance

I want to protect and unprotect multiple sheets except 3 named "pvt1" , "pvt2" , "pvt3" and also with this same code i want to protect active workbook. with vba

Currently using this macro

VBA TO PROTECT .


VBA Code:
Sub ProtectAll()
'
' PROTECTALL Macro
'
ActiveWorkbook.Protect
top:
pass = InputBox("password?")
repass = InputBox("Verify Password")
If Not (pass = repass) Then
MsgBox "you made a boo boo"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro."

End Sub


VBA TO UNPROTECT


VBA Code:
Sub UnProtectAll()
'
' Unprotectall Macro
'
ActiveWorkbook.Unprotect
    Dim ws As Worksheet
    Dim Pwd As String
    Pwd = InputBox("Enter your password to unprotect all worksheets", "Unprotect Worksheets")
    On Error Resume Next
    For Each ws In Worksheets
        ws.Unprotect Password:=Pwd
    Next ws
    If Err <> 0 Then
        MsgBox "You have entered an incorect password. All worksheets could not " & _
"be unprotected.", vbCritical, "Incorect Password"
    End If
    On Error GoTo 0
End Sub
 
Last edited by a moderator:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,133,619
Messages
5,659,917
Members
418,536
Latest member
Tezzies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top