Worksheet Formula to show if tab is hidden

Question

Let's say I have a "sheet1" and "sheet2" in Excel. I want cell A1 of "sheet1" to say TRUE if "sheet2" is visible and FALSE if "sheet2" is hidden.

I know this can be easily done with VBA, but I am wondering if it is possible to do this using a worksheet formula?

PeterH PeterH · Accepted Answer · 2017-08-29T09:35:39

You could use a UserDefined Function, still VBA, but you then call a formula to check,

Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls)

right click on the project name Insert, then Module You should see the code window pop up on the right hand side

Option Explicit
Function IsSheetVisible(rng As Range) As Variant

Application.Volatile
IsSheetVisible = CBool(rng.Parent.Visible = xlSheetVisible)

End Function

Now go back to excel. Into a test cell and type:

=if(issheetvisible('sheet2'!a1),TRUE,FALSE)

(you can use any cell on that sheet to check visibility.)

Worksheet Formula to show if tab is hidden

1 Answers