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

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
--
HTH...

Jim Thomlinson


"Needhelp" wrote:

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

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

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
--
HTH...

Jim Thomlinson


"Needhelp" wrote:

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

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
On Oct 2, 7:52*pm, Needhelp
wrote:
Thanks, I should have been more clear. *some of the worksheets that I want to
protect do not have menu in the name



"Jim Thomlinson" wrote:
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
--
HTH...

Jim Thomlinson

"Needhelp" wrote:

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- Hide quoted text -

- Show quoted text -

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???
--
HTH...

Jim Thomlinson


"Needhelp" wrote:

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

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
--
HTH...

Jim Thomlinson


"Needhelp" wrote:

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

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

"Needhelp" wrote:

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

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
--
HTH...

Jim Thomlinson


"Needhelp" wrote:

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

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

--


Gary

"Needhelp" wrote in message
...
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

"Needhelp" wrote:

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

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
--
HTH...

Jim Thomlinson


"Needhelp" wrote:

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