Hide/unhide Tabs by color

**angeljdiez** · 01-12-2012, 12:14 PM

Good morning.

I'm new in this forum and I know that probably there is something related to this subject here but I don't have enough time to do a good research and need some help

I am creating this Excel book that contains everything related to a project but I have too many tabs and I dont need to see them all at the same time.
So I am trying to create 4 different buttons (Toggle Buttons I think are the best fit) to hide/unhide tabs related to specifics of the project.
I think that sort them by color is the easiest way than by name since there are too many tabs.

So I did a trial hiding/unhiding tabs by the tab name, but I still cannot figure how to do it by tab color, so if any one know how to do it I'll very thankful!!

Ill attach my two files so you can see what I have so far.

Thank you very much in advance.

**royUK** · 01-12-2012, 02:42 PM

Here's some code to adapt

Option Explicit
Option Private Module

Dim ws As Worksheet
Sub HideRed()
    For Each ws In ThisWorkbook.Worksheets
        If ws.Tab.Color = vbRed Then ws.Visible = xlSheetVeryHidden
    Next ws
End Sub
Sub ShowRed()
    For Each ws In ThisWorkbook.Worksheets
        If ws.Tab.Color = vbRed Then ws.Visible = xlSheetVisible
    Next ws
End Sub

**angeljdiez** · 01-12-2012, 03:49 PM

Hey Thanks!!

I ended up with this anyways

Private Sub ToggleButton1_Click()
Dim wks As Worksheet
  If ToggleButton1.Value = True Then
     ToggleButton1.Caption = "Show Sheets"
     'Worksheets("Sheet1").Visible = False
      
     
        On Error Resume Next
    For Each wks In Worksheets
        If wks.Name = "PCO LOG" Or wks.Name = "Transmittal" Or wks.Name = "01" Or wks.Name = "02" Or wks.Name = "03" Or wks.Name = "04" Or wks.Name = "05" Or wks.Name = "06" Or wks.Name = "07" Or wks.Name = "08" Or wks.Name = "09" Or wks.Name = "10" Or wks.Name = "11" Or wks.Name = "12" Or wks.Name = "13" Or wks.Name = "14" Or wks.Name = "15" Or wks.Name = "16" Or wks.Name = "17" Or wks.Name = "18" Or wks.Name = "19" Or wks.Name = "20" Then
                    wks.Visible = False 'xlSheetHidden
        Else
            If wks.Name = "Project Info" Or wks.Name = "Label" Then
                    wks.Visible = True 'xlSheetHidden
            Else
                    wks.Visible = False
            End If
        End If
    Next wks
    On Error GoTo 0
    
     
  Else
     ToggleButton1.Caption = "Hide Sheets"
     'Worksheets("Sheet1").Visible = True
        On Error Resume Next
    For Each wks In Worksheets
        If wks.Name = "PCO LOG" Or wks.Name = "Transmittal" Or wks.Name = "01" Or wks.Name = "02" Or wks.Name = "03" Or wks.Name = "04" Or wks.Name = "05" Or wks.Name = "06" Or wks.Name = "07" Or wks.Name = "08" Or wks.Name = "09" Or wks.Name = "10" Or wks.Name = "11" Or wks.Name = "12" Or wks.Name = "13" Or wks.Name = "14" Or wks.Name = "15" Or wks.Name = "16" Or wks.Name = "17" Or wks.Name = "18" Or wks.Name = "19" Or wks.Name = "20" Then
                    wks.Visible = True 'xlSheetHidden
        Else
            If wks.Name = "Project Info" Or wks.Name = "Label" Then
                    wks.Visible = True 'xlSheetHidden
            Else
                    wks.Visible = False
            End If
                    'wks.Visible = True 'xlSheetHidden
        End If
    Next wks
    On Error GoTo 0
     
  End If
 
End Sub

**royUK** · 01-13-2012, 03:11 AM

Totally different to what you actually asked for! It will work until someone changes the sheet name(s).

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save