Formula to show if Tab is hidden or not

Sweedler

Board Regular
Joined
Nov 13, 2020
Messages
124
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

VBA Code:
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
 
Upvote 0
You could use a small UDF as below:
VBA Code:
Function IsHidden(wsStr As String)
    IsHidden = IIf(Sheets(wsStr).Visible, "SHOWING", "HIDDEN")
End Function

Used in the sheet as below:
Book1
AB
1Sheet1SHOWING
2Sheet2HIDDEN
3Sheet3SHOWING
4Sheet4HIDDEN
5Sheet5SHOWING
Sheet1
Cell Formulas
RangeFormula
B1:B5B1=IsHidden(A1)
 
Upvote 0
Solution
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
 
Upvote 0
In future please post to this board in English only. Thanks
 
Upvote 1

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top