Applying code to all worksheets.

[OfficeUser]

I am trying to do loop through all sheets and change a value but it only affects the sheet for which I am on when I run the code.

Sub ChangeAllWorksheets()
Dim ws As Worksheet
    For Each ws In Worksheets
     Range("A3").Value = "Yes"
    Next ws
End Sub

Any suggestions on what is missing, causing it to not work as planned? Thanks.

 

[VoG]

So this should work

Sub ChangeAllWorksheets()
Dim ws As Worksheet
    For Each ws In Worksheets
     ws.Shapes("lblGreen").Visible = False
    Next ws
End Sub

 

[OfficeUser]

I have to agree, but unfortunately it does not. I checked the name in the name box and it is lblGreen. I am unsure what the issue is.

 

[VoG]

It works for me. Maybe there is a sheet where the name is " lblGreen" or "lblGreen "

 

[OfficeUser]

I just double checked. I am testing using 2 sheets, one is a mere copy of the other.

 

[OfficeUser]

Created a blank sheet, created lblGreen, copied sheet, used code and it worked. Time to go back to my sheet and investigate.

 

[VoG]

You should be able to avoid the error like this (tested)

Sub ChangeAllWorksheets()
Dim ws As Worksheet, shp As Shape
    For Each ws In Worksheets
        For Each shp In ws.Shapes
            If shp.Name = "lblGreen" Then shp.Visible = False
        Next shp
    Next ws
End Sub

 

[OfficeUser]

Well I notice that when I get the 'Specified Item was not found error' and I close the error, lblGreen does disappear. So it is "working", just not properly.

 

[VoG]

Do they disappear if you do this (not a recommended style of coding)

Sub ChangeAllWorksheets()
Dim ws As Worksheet
    For Each ws In Worksheets
        On Error Resume Next
        ws.Shapes("lblGreen").Visible = False
        On Error GoTo 0
    Next ws
End Sub

 

[OfficeUser]

You should be able to avoid the error like this (tested)

Sub ChangeAllWorksheets()
Dim ws As Worksheet, shp As Shape
    For Each ws In Worksheets
        For Each shp In ws.Shapes
            If shp.Name = "lblGreen" Then shp.Visible = False
        Next shp
    Next ws
End Sub

This code is working properly. I wish I could figure out why it would not work the other way.