Checking if sheet is visible

L

LAF

I have a formula on one of my sheets that references cells on another
sheet. However, that sheet it references can be hidden sometimes and
therefore I don't want it referencing it if the sheet is not visible.
Is there a way in the formula on the sheet to check if the sheet it's
referencing is visible?
 
D

Dave Peterson

You could use a UserDefined Function to check for the visibility of the
worksheet.

Option Explicit
Function IsSheetVisible(rng As Range) As Variant

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

End Function


Be aware that this could be one calculation cycle out of date. Hiding/unhiding
a worksheet didn't cause a recalculation for me.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

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

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=if(issheetvisible('sheet1'!a1),'sheet1'!b99,"")

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