Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Checking if sheet is visible
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? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php...fo&userid=9656 View this thread: http://www.excelforum.com/showthread...hreadid=394287 |
#2
|
|||
|
|||
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.) LAF wrote: 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? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php...fo&userid=9656 View this thread: http://www.excelforum.com/showthread...hreadid=394287 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Specific Values From Sheet | Excel Discussion (Misc queries) | |||
How do I determine automatically that a sheet is empty? | Excel Worksheet Functions | |||
Linking References from Multiple Sheets to One Summary Sheet | Setting up and Configuration of Excel | |||
Find a non-blank cell and bring back text a in same row | Excel Worksheet Functions | |||
Copy formula...sheet 2 sheet | New Users to Excel |