Password protect select sheets in a workbook

[Needhelp]

The macro below protects all worksheets in my workbook. I only want to
protect specific worksheets with the names Menu1, Menu2, etc. How would i
modify the macro to accomplish this. I know I can just select each worksheet
and protect it but I would like to learn the more advanced version below.
Sub ProtectSheets()
Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Select
mySheet.protect "Password", True, True, True
Next mySheet
End Sub

 

[Jim Thomlinson]

Sub ProtectSheets()
Dim mySheet As Worksheet
For Each mySheet In Worksheets
if left(mySheet.name, 4) = "Menu" then _
mySheet.protect "Password", True, True, True
Next mySheet
End Sub

 

[Needhelp]

Thanks, I should have been more clear. some of the worksheets that I want to
protect do not have menu in the name

 

[paul.robinson]

Hi
Unless the sheets to protect have something in common, you will have
to specify each sheet in your sub.
e.g.

Sub ProtectSheets()
Dim mySheets as Variant
Dim i as integer
mySheets = VBA.Array("Tax", "Income", "Gross")
For i = 0 to UBound(mySheets)
Worksheets(mySheets(i)).protect "Password", True, True, True
Next i
End Sub

If the set of sheets is going to change, then you will probably need a
userform with a list that presents the sheet names in the workbook.
Users then click on the sheets they want to protect and that array of
sheet names is then fed into this sub

e.g.

Sub ProtectSheets(mySheets as Variant)
Dim i as integer
For i = 0 to UBound(mySheets)
Worksheets(mySheets(i)).protect "Password", True, True, True
Next i
End Sub

regards
Paul

 

[Jim Thomlinson]

How is the macro supposed to know which sheets you want to protect. Is there
any kind of a flag or pattern to look for that defines whether the sheet
needs to be protected???

 

[Needhelp]

Thanks to all,
The below macro is working. I just need to add the rest of the worksheet
names to the array.
Sub ProtectSheets1()
Dim mySheet As Worksheet

For Each mySheet In Worksheets(Array("Analysis", "Menu1"))

mySheet.Select
mySheet.protect "Password", True, True, True
Next mySheet
End Sub

 

[Gary Keramidas]

you could also use something like this, in any case there is no need to
select.

Sub ProtectSheets1()
Dim mySheet As Variant
Dim i As Long
mySheet = Array("Sheet1", "Sheet2")
For i = LBound(mySheet) To UBound(mySheet)
Worksheets(mySheet(i)).Protect "Password", True, True, True
Next
End Sub