+ Reply to Thread
Results 1 to 3 of 3

Hiding rows in one tab based on cell value in another

  1. #1
    Registered User
    Join Date
    01-03-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007
    Posts
    3

    Hiding rows in one tab based on cell value in another

    Ok-so someone else had supplied this code to me to hide rows in my tab named "Annual Record" based on a cell's value in another tab. The other tab's names are WO1, WO2, WO3, etc...

    The code that I currently have is:
    Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)

    Set A = Sheets("Annual Record")
    Set b = Sheets("WO1")
    Set c = Sheets("WO2")
    Set d = Sheets("WO3")
    Set e = Sheets("WO4")
    Set WorkOrder1 = A.Rows("4:7")
    Set WorkOrder2 = A.Rows("8:11")
    Set WorkOrder3 = A.Rows("12:15")
    Set WorkOrder4 = A.Rows("16:19")

    If b.Range("AH5") = "Not Started" Then
    WorkOrder1.Hidden = True
    Else: WorkOrder1.Hidden = False
    End If
    If c.Range("AH5") = "Not Started" Then
    WorkOrder2.Hidden = True
    Else: WorkOrder2.Hidden = False
    End If
    If d.Range("AH5") = "Not Started" Then
    WorkOrder3.Hidden = True
    Else: WorkOrder3.Hidden = False
    End If
    If e.Range("AH5") = "Not Started" Then
    WorkOrder4.Hidden = True
    Else: WorkOrder4.Hidden = False
    End If

    End Sub

    Now this person indicated that if I only had to add a few more tabs, as in WO4, WO5, etc...it wouldn't be a big deal (and I agree). However, I need to add a significant amount of additional tabs. So this person suggested adding a loop or something. If you need more information, let me know, but I think you can follow the trend, 4 rows are hidded on Annual Record for each tab basically.

    Without blowing this code into a book, is there a quicker way to do this if I need to add a bunch more tabs???

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Hiding rows in one tab based on cell value in another

    Take a look at the paragraph about WithEvents in this article: http://www.cpearson.com/excel/Events.aspx

    BTW, please put the code you posted between code tags.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Hiding rows in one tab based on cell value in another

    Guess this is all you need.
    Put it in ThisWorkbook.
    Please Login or Register  to view this content.
    This solution, however, requires you to name your sheets consistently (WO1, WO2, WO3 etc), but that shouldn't be a problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1