Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Shorten code possibaly with a loop?

  1. #1
    Board Regular
    Join Date
    May 2015
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Shorten code possibaly with a loop?

    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.

    Code:
    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

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,645
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Shorten code possibaly with a loop?

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    20,476
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Shorten code possibaly with a loop?

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

    Code:
    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.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,337
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Shorten code possibaly with a loop?

    Or if your sheet names are the same as your codenames
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,545
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Shorten code possibaly with a loop?

    My attempt seeing as id written it:

    Code:
    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

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,645
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Shorten code possibaly with a loop?

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,645
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Shorten code possibaly with a loop?

    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"

    Code:
    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,545
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Shorten code possibaly with a loop?

    You can use intersect for that.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,645
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Shorten code possibaly with a loop?

    Quote Originally Posted by steve the fish View Post
    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    5,545
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Shorten code possibaly with a loop?

    And for interest heres why. Consider this code:

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •