Shorten code possibaly with a loop?

[Mick Peters]

Hi, Could anyone please help with shortening the below code. I tend to search out how to do something and then when I get it working just repeat the step. Here I have a list of 36 users (Column Z from Cell 3 to 38) and for each user there is a work sheet with the tab name the same as the user name. The tab name changes when the user name is changed in one of the cells. What I am trying to do with the below code is to hide the tabs where there is no user name in the corresponding cell. I started with only a few names and it worked fine and as the range for names expanded I added more lines of code. However this last addition has taken the number over 12 and the 13th one and any after have failed to work. I have seen some code wit loops but not full up on how they work. I am eager to learn so if anyone answering would be kind enough to add any helpful comments so I could solve something like this on my own if the future that would be really great.

Private Sub Worksheet_Change(ByVal Target As Range)
If [Z3] <> "" Then
Sheet4.Visible = True
Else
Sheet4.Visible = False
End If
If [Z4] <> "" Then
Sheet5.Visible = True
Else
Sheet5.Visible = False
End If
If [Z5] <> "" Then
Sheet6.Visible = True
Else
Sheet6.Visible = False
End If
If [Z6] <> "" Then
Sheet7.Visible = True
Else
Sheet7.Visible = False
End If
If [Z7] <> "" Then
Sheet8.Visible = True
Else
Sheet8.Visible = False
End If
If [Z8] <> "" Then
Sheet9.Visible = True
Else
Sheet9.Visible = False
End If
If [Z9] <> "" Then
Sheet10.Visible = True
Else
Sheet10.Visible = False
End If
If [Z10] <> "" Then
Sheet11.Visible = True
Else
Sheet11.Visible = False
End If
If [Z11] <> "" Then
Sheet12.Visible = True
Else
Sheet12.Visible = False
End If
If [Z12] <> "" Then
Sheet13.Visible = True
Else
Sheet13.Visible = False
End If
If [Z13] <> "" Then
Sheet14.Visible = True
Else
Sheet14.Visible = False
End If
If [Z14] <> "" Then
Sheet15.Visible = True
Else
Sheet15.Visible = False
End If
If [Z15] <> "" Then
Sheet16.Visible = True
Else
Sheet16.Visible = False
End If
If [Z16] <> "" Then
Sheet17.Visible = True
Else
Sheet17.Visible = False
End If
If [Z17] <> "" Then
Sheet18.Visible = True
Else
Sheet18.Visible = False
End If
If [Z18] <> "" Then
Sheet19.Visible = True
Else
Sheet19.Visible = False
End If
If [Z19] <> "" Then
Sheet20.Visible = True
Else
Sheet20.Visible = False
End If
If [Z20] <> "" Then
Sheet21.Visible = True
Else
Sheet21.Visible = False
End If
If [Z21] <> "" Then
Sheet22.Visible = True
Else
Sheet22.Visible = False
End If
If [Z22] <> "" Then
Sheet23.Visible = True
Else
Sheet23.Visible = False
End If
If [Z23] <> "" Then
Sheet24.Visible = True
Else
Sheet24.Visible = False
End If
If [Z24] <> "" Then
Sheet25.Visible = True
Else
Sheet25.Visible = False
End If
If [Z25] <> "" Then
Sheet26.Visible = True
Else
Sheet26.Visible = False
End If
If [Z26] <> "" Then
Sheet27.Visible = True
Else
Sheet27.Visible = False
End If
If [Z27] <> "" Then
Sheet28.Visible = True
Else
Sheet28.Visible = False
End If
If [Z28] <> "" Then
Sheet29.Visible = True
Else
Sheet29.Visible = False
End If
If [Z29] <> "" Then
Sheet30.Visible = True
Else
Sheet30.Visible = False
End If
If [Z30] <> "" Then
Sheet31.Visible = True
Else
Sheet31.Visible = False
End If
If [Z31] <> "" Then
Sheet32.Visible = True
Else
Sheet32.Visible = False
End If
If [Z32] <> "" Then
Sheet33.Visible = True
Else
Sheet33.Visible = False
End If
If [Z33] <> "" Then
Sheet34.Visible = True
Else
Sheet34.Visible = False
End If
If [Z34] <> "" Then
Sheet35.Visible = True
Else
Sheet35.Visible = False
End If
If [Z35] <> "" Then
Sheet36.Visible = True
Else
Sheet36.Visible = False
End If
End Sub

Thank you in advance,
Mick

 

[My Aswer Is This]

So are you putting this same script in all your sheets?
And you said sheet name changes when cell value changes.

I think it would be nice to know what your overall objective is.
And it looks like to me since your using cell change event. Every time you modify any cell value this script will run.

 

[shg]

This should work if the order of appearance of sheets follows their codenames:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i             As Long

  For i = 3 To 36
    If Worksheets(i + 1).Visible <> Cells(i, "Z") <> "" Then
      Worksheets(i + 1).Visible = Not (Worksheets(i + 1).Visible)
    End If
  Next i
End Sub

It should also avoid killing Undo when no sheet visibility needs to change.

But -- untested in either aspect.

 

[Fluff]

Or if your sheet names are the same as your codenames

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Long

  For i = 3 To 36
      Worksheets("sheet" & i + 1).Visible = Len(Cells(i, "Z"))
  Next i
End Sub

 

[steve the fish]

My attempt seeing as id written it:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, sh As Worksheet
Dim shFirst As Long, shLast As Long

Set rng = Range("Z3:Z35") 'range to check
shFirst = 4 'first sheet codename
shLast = 36 'last sheet codename

If Not Intersect(Target, rng) Is Nothing Then
    For Each sh In ThisWorkbook.Worksheets
        If Replace(sh.CodeName, "Sheet", "") >= shFirst Then
            If Replace(sh.CodeName, "Sheet", "") <= shLast Then
                If IsError(Application.Match(sh.Name, rng, 0)) Then
                    sh.Visible = False
                Else
                    sh.Visible = True
                End If
            End If
        End If
    Next
End If
            
End Sub

 

[My Aswer Is This]

When I try using any of these scripts if I change any value in any cell on the sheet the script runs. Not just column Z.

 

[My Aswer Is This]

When I write scripts like this I always use something like this.
Which causes the script to only run if a change is made to column "Z"

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i   As Long
If Target.Column = 26 And Target.Row > 2 Then
  For i = 3 To 36
    If Worksheets(i + 1).Visible <> Cells(i, "Z") <> "" Then
      Worksheets(i + 1).Visible = Not (Worksheets(i + 1).Visible)
    End If
  Next i
End If
End Sub

 

[steve the fish]

You can use intersect for that.

 

[My Aswer Is This]

You can use intersect for that.

Yes I know intersect also works.
I used the other scripts prior to seeing yours. None of the previous scripts had either.

 

[steve the fish]

And for interest heres why. Consider this code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 26 And Target.Row > 2 Then
    MsgBox "Column and row"
End If

If Not Intersect(Target, Range("Z3:Z100")) Is Nothing Then
    MsgBox "Intersect"
End If

End Sub

Now change a cell in the range, say Z3. You get two message boxes yes? Now try this. Put a 1 in A1 and a 2 in B1. Copy these cells. Now paste them into Y3. Note the message box.