Protecting Multiple Sheets in a Workbook at One Time

[Arianava]

Hi, there!!

When searching the internet for an answer as to how I could protect all sheets in my workbook at one time, instead of having to protect each one individually, the only thing I found that would actually be a perfect fit was creating a macro.

I've never used a macro before, and know absolutely nothing about how to create one!

Given that, I attempted the following code that I found in another forum:

Sub Protect_Me()
Dim x As Long
For x = 1 To Worksheets.Count
If Sheets(x).ProtectContents Then
Sheets(x).Unprotect Password:="MyPass"
Else
Sheets(x).Protect Password:="MyPass"
End If
Next
End Sub
​

When I tried to run it in Excel 2007, I got a Sytax Error, which shows the first line (Sub Protect_Me()) highlighted in yellow, with a little yellow arrow pointing to it. I'm assuming the program doesn't like this line. I couldn't tell you why!

Can someone tell me why, and tell me how to correct it so that it works?

Thanks bunches!!

Ariana

 

[wigi]

This code works fine for me, also Excel 2007.

Where did you paste that code?

 

[Yahya]

try this code will protect all sheets with PassWord "MyPass" change it as you want

Sub ProSheets()
Dim ws As Worksheet
For Each ws In Sheets
    ws.Protect "MyPass"
Next ws
End Sub

 

[byaniess]

try this code will protect all sheets with PassWord "MyPass" change it as you want

Sub ProSheets()
Dim ws As Worksheet
For Each ws In Sheets
    ws.Protect "MyPass"
Next ws
End Sub

Awesome, works fabulous! However, how would I toggle this so it would unprotect all the sheets at once with the password?

 

[Yahya]

Awesome, works fabulous! However, how would I toggle this so it would unprotect all the sheets at once with the password?

just change word Protect to UnProtect

Sub ProSheets()
Dim ws As Worksheet
For Each ws In Sheets
    ws.UnProtect "MyPass"
Next ws
End Sub

 

[byaniess]

Fantastic! This worked. Thank you so much for your help!

 

[Yahya]

Fantastic! This worked. Thank you so much for your help!

you are welcome

 

[Arianava]

Yahya,

Sorry for the delay in responding - I haven't had a chance to play with the macro until now. I did get your code to work! Thanks! Now, I just need to learn how to navigate macros properly!

Thanks bunches!