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

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

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 

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

Since this is a VBA question, you would be better off asking it in forum707: VBA Visual Basic for Applications (Microsoft)

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.

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

(OP)
OK Thank you

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