VBA/Formula to determine if Sheet is hidden or not

[Echidnadsn]

Good afternoon. I've been looking around trying to find something that would work for me but can't really seem to find anything, so decided to register and post.

Quick Explanation
I am looking for a method (either VBA or just formula) to state if a sheet is hidden or not, and have it update as states change. The need for this is i want it to drive a formula that will ignore values on a sheet if its hidden.

Longer Explanation
i have a certification workbook that has several different departments. Not all departments are being certified for every location. The users hide the sheets they aren't being certified on. Right now everything flows to a hidden calculations sheet. I basically want to have on that calculation sheet something like "If SHEET=Hidden, Dont Calculate. If SHEET=Visible: Calculate Columns A:D"

Does that make sense?

 

[Micron]

I gather that copies of the main workbook will contain this code, and who's using a particular copy dictates which sheets might be hidden. Something the user does would cause the code to loop through all the visible sheets and perform a recalc on each one of them at that point, and ignore the ones that are hidden. Or maybe you're looking for a formula, in which case I can't help. In the case of code, each user wb would have to be an .xlsm file or else code could not be used.

 

[Echidnadsn]

Right, so there is a main Master file that is downloaded by everyone who needs it, and then they save locally and hide/show sheets based on the services they need. I'm using VBA in a couple other areas, so its already saved as a macro file. I'm thinking of it having a Reset button that would reset all entered values to default which could also drive the hidden/visible calculator if its not something that could be done live.

 

[Micron]

I still don't know what you mean by 'calculate'. That is something you can cause the workbook or sheet to do in order to recalculate formulas in case values have changed. If you expect a formula using a built in function to determine whether or not a sheet is visible, I don't think it's possible but would be happy to be proved wrong. However, I suspect that your formula could use a UDF (user defined function). The function would test if the sheet name in the formula is visible or not. If not, return 0, "" or whatever you want. If it is, then let the formula do its thing. The sheet names could be passed in the function call or get it from a helper cell. So something like

=If(myUDF("Sheet1"), 5+5, 0)

Translation: if myUDF returns True (sheet is visible) then cell =5+5, else cell =0
You could just as easily return False (sheet is hidden) or anything else. The function needs to return a data type that is compatible with the cell format. Each formula needs to pass a different sheet name to the function, or a different helper cell reference for each row that you have these formulas on.