Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

MJV8198

Technical User
Oct 25, 2012
35
US
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

Code:
Macro 1

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

Code:
Macro 2

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

ActiveSheet.EnableSelection = 1
 
Since this is a VBA question, you would be better off asking it in forum707

And, please format your code with TGML tags. It would be so much easier to read.


---- Andy

There is a great need for a sarcasm font.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top