Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next... what's wrong with my code???
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:P,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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next... what's wrong with 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 ' added line sh.select Range("A:L,N:P,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 -- Regards, Tom Ogilvy "PixelSyl" wrote: 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:P,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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next... what's wrong with my code???
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:P,R:T,V:X,Z:AB").Validation rather than sh.Activate ActiveSheet.Range("...").Select Selection.Valation... -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "PixelSyl" wrote in message ... 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:P,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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next... what's wrong with my code???
Tom, you're a genius. been reading lots of your posts and you're THE Man!
Merci beaucoup have a great one ! -- "Chaos, where brilliant dreams are born" "Tom Ogilvy" wrote: 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:P,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 -- Regards, Tom Ogilvy "PixelSyl" wrote: 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:P,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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each Next... what's wrong with my code???
Great idea Tim, I'll try that one also.
Merci! -- "Chaos, where brilliant dreams are born" "Tim Zych" wrote: 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:P,R:T,V:X,Z:AB").Validation rather than sh.Activate ActiveSheet.Range("...").Select Selection.Valation... -- Tim Zych www.higherdata.com Compare data in worksheets and find differences with Workbook Compare A free, powerful, flexible Excel utility "PixelSyl" wrote in message ... 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:P,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help ... what wrong with this code | Excel Programming | |||
HELP! What's wrong with my code? | Excel Programming | |||
HELP! What's wrong with my code? | Excel Programming | |||
What is wrong with this code? | Excel Programming | |||
What's wrong with the code,pls hv a look | Excel Programming |