CODEWITHSUNDEEP
vbahidemultiple-columnsworksheet
il_Paco
il_Paco

Reputation: 3

Hide columns and multiple sheets using loop

I currently have a loop that works great to hide columns based on multiple dropdown cells. I would also like to add code to hide sheets based on the same drop downs, but I'm not sure how to add on to my For Each Cell In Range to accommodate that. I have pasted what I have to hide the columns below. Any help would be greatly appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Range("$A$30:$A$38")

   If cell = "Descriptor 1" Or cell = "Descriptor 2" Then
    Columns("B:F").EntireColumn.Hidden = False
   Exit For
   Else
    Columns("B:F").EntireColumn.Hidden = True
End If
Next Cell

Upvotes: 0

Views: 1289

Answers (2)

il_Paco
il_Paco

Reputation: 3

@YowE3K Your code is great. But I had a problem with the tab names being in short form and my descriptors being in full form. So, I took your original code, added a "HideTab" for each tab, and switched the topline HideTab = False to true and reversed it in the 4th line HideTab (See below). I'm sure there is a faster way, but this worked like a charm. Thank you very much for your help! You pointed me in the right direction.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim HideIt As Boolean

HideIt = True
    For Each cell In Range("$A$30:$A$38")    
        If cell.Value = "Descriptor 1" Or
        cell.Value = "Descriptor 2" Then
       HideIt = False
       Exit For
   End If
Next Cell
Columns("B:F").EntireColumn.Hidden = True


Dim HideTab1 As Boolean
    HideTab1 = False
    For Each cell In Range("$A$30:$A$38")
        If cell = "Descriptor1" Then
            HideTab1 = True
            Exit For
        End If
    Next cell
    Sheets("Desc1").Visible = HideTab1

Dim HideTab2 As Boolean
    HideTab2 = False
    For Each cell In Range("$A$30:$A$38")
        If cell = "Descriptor2" Then
            HideTab2 = True
            Exit For
        End If
    Next cell
    Sheets("Desc2").Visible = HideTab2

Dim HideTab3 As Boolean
    HideTab3 = False
    For Each cell In Range("$A$30:$A$38")
        If cell = "Descriptor3" Then
            HideTab3 = True
            Exit For
        End If
    Next cell
    Sheets("Desc3").Visible = HideTab3
End Sub

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

You can use something like Worksheets("sheet_to_hide").Visible = xlSheetHidden to hide a sheet and Worksheets("sheet_to_unhide").Visible = xlSheetVisible to unhide it again.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim HideIt As Boolean

    HideIt = True
    For Each cell In Range("$A$30:$A$38")    
       If cell.Value = "Descriptor 1" Or _
          cell.Value = "Descriptor 2" Then
           HideIt = False
           Exit For
       End If
    Next Cell

    If HideIt Then
        Columns("B:F").Hidden = True
        Worksheets("Sheet1").Visible = xlSheetHidden
        Worksheets("Sheet2").Visible = xlSheetHidden
    Else
        Columns("B:F").Hidden = False
        Worksheets("Sheet1").Visible = xlSheetVisible
        Worksheets("Sheet2").Visible = xlSheetVisible
    End If
End Sub

If the worksheets are to be hidden / made visible depending on whether their sheet name appears in your range, then I would suggest the following modification:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim HideIt As Boolean

    'Don't do anything if there was no change to A30:A38
    If Intersect(Target, Range("$A$30:$A$38")) Is Nothing Then Exit Sub

    HideIt = True
    For Each cell In Range("$A$30:$A$38")
       If cell.Value = "Descriptor 1" Or _
          cell.Value = "Descriptor 2" Then
           HideIt = False
           Exit For
       End If
    Next cell
    Columns("B:F").Hidden = HideIt

    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> ActiveSheet.Name Then
            'See if sheet name exists in A30:A38
            'Hide the sheet if doesn't, make it visible if it does
            ws.Visible = Not IsError(Application.Match(ws.Name, Range("$A$30:$A$38"), 0))
        End If
    Next
End Sub

Upvotes: 1

Related Questions