Results 1 to 5 of 5

Thread: Need a shorter VBA code: Hide and unhide sheets based on presence of name in list

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Member
    Join Date
    May 2020
    Posts
    66
    Rep Power
    5

    Need a shorter VBA code: Hide and unhide sheets based on presence of name in list

    I have a workbook having 7 sheets, named
    MS, CWS, 1, 2, 3, 4, 5


    On sheet MS there is a table in range C4:C8

    I want to hide unhide sheets on the basis of table, except the sheet "CWS"

    (Please see the attachment)


    here is the code:



    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
         
      'hide unhide sheet 1
         If [C4] <> "" Then
            Sheet3.Visible = True
         Else
            Sheet3.Visible = False
         End If
         
       'hide unhide sheet 2
         If [C5] <> "" Then
            Sheet4.Visible = True
         Else
            Sheet4.Visible = False
         End If
         
       'hide unhide sheet 3
         If [C6] <> "" Then
            Sheet5.Visible = True
         Else
            Sheet5.Visible = False
         End If
         
       'hide unhide sheet 4
         If [C7] <> "" Then
            Sheet6.Visible = True
         Else
            Sheet6.Visible = False
         End If
         
       'hide unhide sheet 5
         If [C8] <> "" Then
            Sheet7.Visible = True
         Else
            Sheet7.Visible = False
         End If 
    
    End Sub
    This code can be simplified, but I don't know how.

    Would you please help me to do so.
    Attached Images Attached Images
    Last edited by DocAElstein; 06-11-2020 at 11:59 AM. Reason: Code tags - in editor use # icon for - before posting: [code] your coding here [/code]

Similar Threads

  1. Replies: 8
    Last Post: 06-01-2020, 06:13 PM
  2. macro to hide and unhide columns and rows
    By dkesar in forum Excel Help
    Replies: 1
    Last Post: 12-30-2014, 12:11 PM
  3. VBA To Hide And Unhide Rows
    By paul_pearson in forum Excel Help
    Replies: 10
    Last Post: 05-08-2013, 03:14 AM
  4. Hide/Unhide Columns with Listbox
    By obed_cruz in forum Excel Help
    Replies: 2
    Last Post: 05-28-2011, 07:26 PM
  5. Hide and Unhide Rows and Columns
    By Admin in forum Download Center
    Replies: 0
    Last Post: 05-11-2011, 12:00 AM

Posting Permissions

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