Hello,

I am trying to use the code below to protect all the tabs in an excel file
if the tab color has not been changed or appears grey. The code does not seem
to be checking every tab, it only works for the tab that is active and does
not continue to the next tabs. Can you please help me determine why it is not
working?

Thank you,

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Tab.ColorIndex = xlColorIndexNone Then
ActiveSheet.Protect Password:=" ", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Else
End If
Next wks

MsgBox "The Workbook is Protected"

you need to select each sheet in turn

For Each wks In ActiveWorkbook.Worksheets
wks.select

Mike

"tjh" wrote:

Hello,

I am trying to use the code below to protect all the tabs in an excel file
if the tab color has not been changed or appears grey. The code does not seem
to be checking every tab, it only works for the tab that is active and does
not continue to the next tabs. Can you please help me determine why it is not
working?

Thank you,

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Tab.ColorIndex = xlColorIndexNone Then
ActiveSheet.Protect Password:=" ", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Else
End If
Next wks

MsgBox "The Workbook is Protected"

Perfect, Thanks

"Mike H" wrote:

you need to select each sheet in turn

For Each wks In ActiveWorkbook.Worksheets
wks.select

Mike

"tjh" wrote:

Hello,

I am trying to use the code below to protect all the tabs in an excel file
if the tab color has not been changed or appears grey. The code does not seem
to be checking every tab, it only works for the tab that is active and does
not continue to the next tabs. Can you please help me determine why it is not
working?

Thank you,

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Tab.ColorIndex = xlColorIndexNone Then
ActiveSheet.Protect Password:=" ", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Else
End If
Next wks

MsgBox "The Workbook is Protected"

one way

Sub Protect_All_Sheets()
Dim i As Long
For i = 1 To Worksheets.Count
With Worksheets(i)
If .Tab.ColorIndex = xlColorIndexNone Then
.Protect
End If
End With
Next

End Sub



--


Gary


"tjh" wrote in message
...
Hello,

I am trying to use the code below to protect all the tabs in an excel file
if the tab color has not been changed or appears grey. The code does not seem
to be checking every tab, it only works for the tab that is active and does
not continue to the next tabs. Can you please help me determine why it is not
working?

Thank you,

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
If wks.Tab.ColorIndex = xlColorIndexNone Then
ActiveSheet.Protect Password:=" ", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Else
End If
Next wks

MsgBox "The Workbook is Protected"