none
Code for sheet hide and unhide RRS feed

  • Question

  • Hi,

    Any shorter and better way to do this,

    Private Sub APR_Click()
        If ActiveWorkbook.Sheets("TREAT (4)").Visible <> True Then ActiveWorkbook.Sheets("TREAT (4)").Visible = True
        If ActiveWorkbook.Sheets("TREAT (1)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (1)").Visible = False
         If ActiveWorkbook.Sheets("TREAT (2)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (2)").Visible = False
          If ActiveWorkbook.Sheets("TREAT (3)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (3)").Visible = False
           If ActiveWorkbook.Sheets("TREAT (5)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (5)").Visible = False
            If ActiveWorkbook.Sheets("TREAT (6)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (6)").Visible = False
             If ActiveWorkbook.Sheets("TREAT (7)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (7)").Visible = False
              If ActiveWorkbook.Sheets("TREAT (8)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (8)").Visible = False
               If ActiveWorkbook.Sheets("TREAT (9)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (9)").Visible = False
                If ActiveWorkbook.Sheets("TREAT (10)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (10)").Visible = False
                 If ActiveWorkbook.Sheets("TREAT (11)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (11)").Visible = False
                  If ActiveWorkbook.Sheets("TREAT (12)").Visible <> False Then ActiveWorkbook.Sheets("TREAT (12)").Visible = False
                  
        If ActiveWorkbook.Sheets("VAC (4)").Visible <> True Then ActiveWorkbook.Sheets("VAC (4)").Visible = True
        If ActiveWorkbook.Sheets("VAC (1)").Visible <> False Then ActiveWorkbook.Sheets("VAC (1)").Visible = False
         If ActiveWorkbook.Sheets("VAC (2)").Visible <> False Then ActiveWorkbook.Sheets("VAC (2)").Visible = False
          If ActiveWorkbook.Sheets("VAC (3)").Visible <> False Then ActiveWorkbook.Sheets("VAC (3)").Visible = False
           If ActiveWorkbook.Sheets("VAC (5)").Visible <> False Then ActiveWorkbook.Sheets("VAC (5)").Visible = False
            If ActiveWorkbook.Sheets("VAC (6)").Visible <> False Then ActiveWorkbook.Sheets("VAC (6)").Visible = False
             If ActiveWorkbook.Sheets("VAC (7)").Visible <> False Then ActiveWorkbook.Sheets("VAC (7)").Visible = False
              If ActiveWorkbook.Sheets("VAC (8)").Visible <> False Then ActiveWorkbook.Sheets("VAC (8)").Visible = False
               If ActiveWorkbook.Sheets("VAC (9)").Visible <> False Then ActiveWorkbook.Sheets("VAC (9)").Visible = False
                If ActiveWorkbook.Sheets("VAC (10)").Visible <> False Then ActiveWorkbook.Sheets("VAC (10)").Visible = False
                 If ActiveWorkbook.Sheets("VAC (11)").Visible <> False Then ActiveWorkbook.Sheets("VAC (11)").Visible = False
                  If ActiveWorkbook.Sheets("VAC (12)").Visible <> False Then ActiveWorkbook.Sheets("VAC (12)").Visible = False
                  
        If ActiveWorkbook.Sheets("BREED (4)").Visible <> True Then ActiveWorkbook.Sheets("BREED (4)").Visible = True
        If ActiveWorkbook.Sheets("BREED (1)").Visible <> False Then ActiveWorkbook.Sheets("BREED (1)").Visible = False
         If ActiveWorkbook.Sheets("BREED (2)").Visible <> False Then ActiveWorkbook.Sheets("BREED (2)").Visible = False
          If ActiveWorkbook.Sheets("BREED (3)").Visible <> False Then ActiveWorkbook.Sheets("BREED (3)").Visible = False
           If ActiveWorkbook.Sheets("BREED (5)").Visible <> False Then ActiveWorkbook.Sheets("BREED (5)").Visible = False
            If ActiveWorkbook.Sheets("BREED (6)").Visible <> False Then ActiveWorkbook.Sheets("BREED (6)").Visible = False
             If ActiveWorkbook.Sheets("BREED (7)").Visible <> False Then ActiveWorkbook.Sheets("BREED (7)").Visible = False
              If ActiveWorkbook.Sheets("BREED (8)").Visible <> False Then ActiveWorkbook.Sheets("BREED (8)").Visible = False
               If ActiveWorkbook.Sheets("BREED (9)").Visible <> False Then ActiveWorkbook.Sheets("BREED (9)").Visible = False
                If ActiveWorkbook.Sheets("BREED (10)").Visible <> False Then ActiveWorkbook.Sheets("BREED (10)").Visible = False
                 If ActiveWorkbook.Sheets("BREED (11)").Visible <> False Then ActiveWorkbook.Sheets("BREED (11)").Visible = False
                  If ActiveWorkbook.Sheets("BREED (12)").Visible <> False Then ActiveWorkbook.Sheets("BREED (12)").Visible = False
                  
        If ActiveWorkbook.Sheets("EXT4 (4)").Visible <> True Then ActiveWorkbook.Sheets("EXT4 (4)").Visible = True
        If ActiveWorkbook.Sheets("EXT4 (1)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (1)").Visible = False
         If ActiveWorkbook.Sheets("EXT4 (2)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (2)").Visible = False
          If ActiveWorkbook.Sheets("EXT4 (3)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (3)").Visible = False
           If ActiveWorkbook.Sheets("EXT4 (5)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (5)").Visible = False
            If ActiveWorkbook.Sheets("EXT4 (6)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (6)").Visible = False
             If ActiveWorkbook.Sheets("EXT4 (7)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (7)").Visible = False
              If ActiveWorkbook.Sheets("EXT4 (8)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (8)").Visible = False
               If ActiveWorkbook.Sheets("EXT4 (9)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (9)").Visible = False
                If ActiveWorkbook.Sheets("EXT4 (10)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (10)").Visible = False
                 If ActiveWorkbook.Sheets("EXT4 (11)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (11)").Visible = False
                  If ActiveWorkbook.Sheets("EXT4 (12)").Visible <> False Then ActiveWorkbook.Sheets("EXT4 (12)").Visible = False
                  
        If ActiveWorkbook.Sheets("CTLH5 (4)").Visible <> True Then ActiveWorkbook.Sheets("CTLH5 (4)").Visible = True
        If ActiveWorkbook.Sheets("CTLH5 (1)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (1)").Visible = False
         If ActiveWorkbook.Sheets("CTLH5 (2)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (2)").Visible = False
          If ActiveWorkbook.Sheets("CTLH5 (3)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (3)").Visible = False
           If ActiveWorkbook.Sheets("CTLH5 (5)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (5)").Visible = False
            If ActiveWorkbook.Sheets("CTLH5 (6)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (6)").Visible = False
             If ActiveWorkbook.Sheets("CTLH5 (7)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (7)").Visible = False
              If ActiveWorkbook.Sheets("CTLH5 (8)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (8)").Visible = False
               If ActiveWorkbook.Sheets("CTLH5 (9)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (9)").Visible = False
                If ActiveWorkbook.Sheets("CTLH5 (10)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (10)").Visible = False
                 If ActiveWorkbook.Sheets("CTLH5 (11)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (11)").Visible = False
                  If ActiveWorkbook.Sheets("CTLH5 (12)").Visible <> False Then ActiveWorkbook.Sheets("CTLH5 (12)").Visible = False




                   
     End Sub

    Friday, July 22, 2016 5:42 PM

All replies

  • Re:  make it shorter

    Private Sub APR_Click()
      ActiveWorkbook.Sheets("TREAT (4)").Visible = True
      ActiveWorkbook.Sheets("TREAT (1)").Visible = False
      ActiveWorkbook.Sheets("TREAT (2)").Visible = False
      ActiveWorkbook.Sheets("TREAT (3)").Visible = False
      ActiveWorkbook.Sheets("TREAT (5)").Visible = False
      ActiveWorkbook.Sheets("TREAT (6)").Visible = False
      ActiveWorkbook.Sheets("TREAT (7)").Visible = False
      ActiveWorkbook.Sheets("TREAT (8)").Visible = False
      ActiveWorkbook.Sheets("TREAT (9)").Visible = False
      ActiveWorkbook.Sheets("TREAT (10)").Visible = False
      ActiveWorkbook.Sheets("TREAT (11)").Visible = False
      ActiveWorkbook.Sheets("TREAT (12)").Visible = False
                  
      ActiveWorkbook.Sheets("VAC (4)").Visible = True
      ActiveWorkbook.Sheets("VAC (1)").Visible = False
      ActiveWorkbook.Sheets("VAC (2)").Visible = False
      ActiveWorkbook.Sheets("VAC (3)").Visible = False
      ActiveWorkbook.Sheets("VAC (5)").Visible = False
      ActiveWorkbook.Sheets("VAC (6)").Visible = False
      ActiveWorkbook.Sheets("VAC (7)").Visible = False
      ActiveWorkbook.Sheets("VAC (8)").Visible = False
      ActiveWorkbook.Sheets("VAC (9)").Visible = False
      ActiveWorkbook.Sheets("VAC (10)").Visible = False
      ActiveWorkbook.Sheets("VAC (11)").Visible = False
      ActiveWorkbook.Sheets("VAC (12)").Visible = False

      ActiveWorkbook.Sheets("BREED (4)").Visible = True
      ActiveWorkbook.Sheets("BREED (1)").Visible = False
      ActiveWorkbook.Sheets("BREED (2)").Visible = False
      ActiveWorkbook.Sheets("BREED (3)").Visible = False
      ActiveWorkbook.Sheets("BREED (5)").Visible = False
      ActiveWorkbook.Sheets("BREED (6)").Visible = False
      ActiveWorkbook.Sheets("BREED (7)").Visible = False
      ActiveWorkbook.Sheets("BREED (8)").Visible = False
      ActiveWorkbook.Sheets("BREED (9)").Visible = False
      ActiveWorkbook.Sheets("BREED (10)").Visible = False
      ActiveWorkbook.Sheets("BREED (11)").Visible = False
      ActiveWorkbook.Sheets("BREED (12)").Visible = False
     
      ActiveWorkbook.Sheets("EXT4 (4)").Visible = True
      ActiveWorkbook.Sheets("EXT4 (1)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (2)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (3)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (5)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (6)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (7)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (8)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (9)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (10)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (11)").Visible = False
      ActiveWorkbook.Sheets("EXT4 (12)").Visible = False
     
      ActiveWorkbook.Sheets("CTLH5 (4)").Visible = True
      ActiveWorkbook.Sheets("CTLH5 (1)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (2)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (3)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (5)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (6)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (7)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (8)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (9)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (10)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (11)").Visible = False
      ActiveWorkbook.Sheets("CTLH5 (12)").Visible = False
     End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0


    • Edited by James Cone Wednesday, September 21, 2016 11:07 PM
    Friday, July 22, 2016 11:53 PM
  • Re: shorter still

    Private Sub APR_Click()
      Dim arrNums As Variant
      Dim N As Variant
     
      arrNums = Array(1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12)
     
      ActiveWorkbook.Sheets("TREAT (4)").Visible = True
      ActiveWorkbook.Sheets("VAC (4)").Visible = True
      ActiveWorkbook.Sheets("BREED (4)").Visible = True
      ActiveWorkbook.Sheets("EXT4 (4)").Visible = True
      ActiveWorkbook.Sheets("CTLH5 (4)").Visible = True

      For N = LBound(arrNums) To UBound(arrNums)
        ActiveWorkbook.Sheets("TREAT (" & N & ")").Visible = False
        ActiveWorkbook.Sheets("VAC (" & N & ")").Visible = False
        ActiveWorkbook.Sheets("BREED (" & N & ")").Visible = False
        ActiveWorkbook.Sheets("EXT4 (" & N & ")").Visible = False
        ActiveWorkbook.Sheets("CTLH5 (" & N & ")").Visible = False
      Next
     End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0
    (free & commercial excel programs)




    • Edited by James Cone Wednesday, September 21, 2016 11:08 PM
    Saturday, July 23, 2016 12:11 AM
  • Hi,

    Thank you.

    run time erre "g":  subscript out range.

    >> ActiveWorkbook.Sheets("TREAT (4)").Visible = True
      ActiveWorkbook.Sheets("VAC (4)").Visible = True
      ActiveWorkbook.Sheets("BREED (4)").Visible = True
      ActiveWorkbook.Sheets("EXT4 (4)").Visible = True
      ActiveWorkbook.Sheets("CTLH5 (4)").Visible = True   <<< working

    >> For N = LBound(arrNums) To UBound(arrNums)
        ActiveWorkbook.Sheets("TREAT (" & N & ")").Visible = False
        ActiveWorkbook.Sheets("VAC (" & N & ")").Visible = False
        ActiveWorkbook.Sheets("BREED (" & N & ")").Visible = False
        ActiveWorkbook.Sheets("EXT4 (" & N & ")").Visible = False
        ActiveWorkbook.Sheets("CTLH5 (" & N & ")").Visible = False
      Next<<< not working.

    any change in code ?

    regards

    Saturday, July 23, 2016 3:19 PM
  • Hi  James Cone,

    Thank you.

    How if sheets already hidden ? It sends an error.

    regards


    Saturday, July 23, 2016 3:21 PM
  • Re:  subscript out of range...  ActiveWorkbook.Sheets("TREAT (4)").Visible = True

    That means a sheet with that name does not exist in the workbook.

    '---
    Jim Cone
    Saturday, July 23, 2016 6:11 PM
  • Hi James Cone,

    Any solution to this ? please help.

    Regards

    Sunday, July 24, 2016 12:09 AM
  • dr...
    Re: any solution

    Separate code for each workbook is one solution.
    Using On Error Resume Next is another
    Checking whether each sheet exists is another.

    You had to have this problem with your original code.
    '---
    Jim Cone
    Sunday, July 24, 2016 12:49 AM
  • No,

    This problem is not with long code. I wanted shorter and faster code. For another workbook with more sheets.

    Regards

    Sunday, July 24, 2016 5:35 AM
  • Hi drsantoshsinghrathore,

    did you try to check sheet names using loops.

    you can store the sheet name in variable that you want to visible and in for loop in If condition you can check all sheets and if it not match with variable's name then make it hidden. you can find the example below.

    it is just for 1 sheet but I find that you have more then 4 sheets to be visible. so in that case you can use "Or" Operator in If condition to check multiple sheets.

    Sub WorksheetLoop()
    
             Dim WS_Count As Integer
             Dim I As Integer
             Dim shtname As String
             shtname = "Sheet1p"
          
             WS_Count = ActiveWorkbook.Worksheets.Count
          
             For I = 1 To WS_Count
    
                If ActiveWorkbook.Worksheets(I).Name = shtname Then
                On Error Resume Next
                ActiveWorkbook.Worksheets(I).Visible = True
                
                Else
                On Error Resume Next
                 ActiveWorkbook.Worksheets(I).Visible = False
                 
                 End If
             Next I
    
          End Sub
     

    Regards

    Deepak

    Monday, July 25, 2016 2:54 AM
    Moderator
  • Probably OP wanted to reverse the visibility of sheets...

    " If ActiveWorkbook.Sheets("BREED (7)").Visible <> False Then ActiveWorkbook.Sheets("BREED (7)").Visible = False"

    Just small correction needed..in the codes

    QuestionPrivate Sub APR_Click()

      Dim arrNums As Variant
      Dim N As Variant

      arrNums = Array(1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12)

        For N = LBound(arrNums) To UBound(arrNums)
        ActiveWorkbook.Sheets("TREAT (" & N & ")").Visible = not(ActiveWorkbook.Sheets("TREAT (" & N & ")").Visible)

    ........

     Next
     End Sub



    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Saturday, July 30, 2016 4:13 AM
    Answerer