Protect/Unprotect all tabs with set password

[BobbyConkers]

Good day all.

I have a spreadsheet with 80 (and growing) tabs. When this file is distributed, I would like all tabs to be protected with a password.

I have VBA which does this:

Dim wSheet As Worksheet
Dim Pwd As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
For Each wSheet In Worksheets
wSheet.Protect Password:=xyz
Next wSheet

This works, but it does not actually assign that password to those sheets, so effectively, anyone can simply click "Unprotect Sheet" and they're in! This password simply stops people protecting all, which isn't especially useful.

What I need is to protect all those worksheets with a password which is required when unprotecting. The same password for all sheets is sufficient. Is this possible please?

(If so, another VBA which could be used to unprotect all, based on those passwords, would also be useful!)

Many thanks in advance.

 

[Fluff]

Swap xyz for Pwd

 

[BobbyConkers]

Swap xyz for Pwd

Chortle.

First of all, thank you! Second, wtf? When I looked for macros to do this, I had no idea "Pwd" was not just a sub for the password!

This has worked perfectly, I suppose then the only thing I would like would be to set my own password (obviously not "xyz"). Is this possible?

 

[sparky2205]

Just replace "Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")"
with
Pwd = "your password"
OR
Forget about the "Pwd =" and use
.protect password:= "your password"

The benefit of using "Pwd = "your password" is if you have to reset the password in a number of places.
But if you're just using it once then you don't need it.
By the way there are a number of options you can use with .protect like allowformattingrows, allowformattingcolumns, allowinsertingrows etc. Worth checking out.