For Each Next... what's wrong with my code???

[PixelSyl]

Hi all,
New to this group, love what I've found so far, you've been of great help in
writing this code, but ...
Can't get it to loop thru all the sheets. Only works for the active worksheet.
Can anyone see my booboo?
Merci!
Sylvie

My Code:

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary", "Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

Range("A:L,N,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

 

[Tom Ogilvy]

Sub dataval()

Dim sh As Worksheet
Dim Rng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ActiveWorkbook.Worksheets

If IsError(Application.Match(sh.Name, Array("Source", "WBS_USD",
"OLDWBS_USD", "Sales Admin Summary", _
"France Summary", "GSC Summary", "HR Summary", "Legal Summary", "Mkt
Summary", "North America Summary", "Distribution Summary", _
"Corpo Dev Summary", "Bus Dev Summary", "Administration Summary",
"R&D Summary", "vlookup"), 0)) Then

' added line
sh.select


Range("A:L,N,R:T,V:X,Z:AB").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength,
AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="0"
.IgnoreBlank = True
.InCellDropdown = False
.InputTitle = ""
.ErrorTitle = "Protected cells"
.InputMessage = ""
.ErrorMessage = _
"These cells are Read-Only." & Chr(10) & "Please select
CANCEL to clear this alert."
.ShowInput = True
.ShowError = True
End With
Range("A1").Select

End If

Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

 

[Tim Zych]

Looks like you're missing sheet activation before the range selection, e.g.
sh.Activate

However, selecting anything is raraly needed in Excel. Better to reference
the range directly.

With sh.Range("A:L,N,R:T,V:X,Z:AB").Validation

rather than

sh.Activate
ActiveSheet.Range("...").Select
Selection.Valation...

 

[PixelSyl]

Tom, you're a genius. been reading lots of your posts and you're THE Man!
Merci beaucoup have a great one !

 

[PixelSyl]

Great idea Tim, I'll try that one also.
Merci!