Formula to show if Tab is hidden or not

[Sweedler]

Hello

I have a tab the lists all the tabs in a workbook. Several of the tabs that I have do not have any information and are "HIDDEN". But I need to have an overview of this on the index tab (that is the one that lists all the tab names. Does anyone know a formula that I can use to list a "HIDDEN" / "SHOWING" value in the column next to the filename. It would make my life a whole lot easier.

 

[HighAndWilder]

Hello

I have a tab the lists all the tabs in a workbook. Several of the tabs that I have do not have any information and are "HIDDEN". But I need to have an overview of this on the index tab (that is the one that lists all the tab names. Does anyone know a formula that I can use to list a "HIDDEN" / "SHOWING" value in the column next to the filename. It would make my life a whole lot easier.

Place this code in a new worksheet code module and when the sheet is activated the list is updated.

You can place the code in the Index worksheet code module when you are happy with it.

Private Sub Worksheet_Activate()
Dim Ws As Worksheet

  ActiveSheet.Range("A2:B200").Cells.ClearContents
  
  ActiveSheet.Range("A1:B1").Value = Array("Worksheet", "Hidden")

  For Each Ws In Worksheets
    Cells(Ws.Index + 1, 1).Value = Ws.Name
    If Not Ws.Visible Then
      Cells(Ws.Index + 1, 2).Value = "Yes"
    End If
  Next Ws

End Sub

 

[Georgiboy]

You could use a small UDF as below:

Function IsHidden(wsStr As String)
    IsHidden = IIf(Sheets(wsStr).Visible, "SHOWING", "HIDDEN")
End Function

Used in the sheet as below:

Book1
	A	B
1	Sheet1	SHOWING
2	Sheet2	HIDDEN
3	Sheet3	SHOWING
4	Sheet4	HIDDEN
5	Sheet5	SHOWING
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=IsHidden(A1)

 

[Sweedler]

Hej

Jag tog lösning nummer två här för att jag läste den först och det fungerade. Tack båda för er tid

 

[Fluff]

In future please post to this board in English only. Thanks