Condition based on worksheet visibility? Please help!

[juchelka.shopping]

I have workbook containing several worksheets (Excel for Mac 2004). I
would like to create formula in cell(s) on one worksheet based on
visibility (hidden status) of other individual worksheets. Let's say if
the particular worksheet is hidden, set value to zero, otherwise set
value to 1. I know how to approach the formula in general, What I don't
know is, how to check for hidden status of worksheet (what is the
attribute?). Any ideas?

Thank you very much for any help or direction!

Alex

 

[Jim Gordon MVP]

Hi Alex,

There isn't a worksheet function that will directly indicate whether or
not a worksheet is hidden.

If you can change the value of a cell before you hide a given worksheet
you could use the worksheet function ISBLANK to determine whether or not
that particular cell is empty. If you put an X into the cell before you
hide the sheet then ISBLANK will be FALSE.

If you want a completely automated solution you will need to use a
little visual basic to obtain the status of the Hidden property for the
worksheet.

-Jim

 

[JE McGimpsey]

I have workbook containing several worksheets (Excel for Mac 2004). I
would like to create formula in cell(s) on one worksheet based on
visibility (hidden status) of other individual worksheets. Let's say if
the particular worksheet is hidden, set value to zero, otherwise set
value to 1. I know how to approach the formula in general, What I don't
know is, how to check for hidden status of worksheet (what is the
attribute?). Any ideas?

I've used this User Defined Function in several projects:

Public Function SheetVisible(ByRef reference As Range) As Boolean
Application.Volatile
SheetVisible = reference.Parent.Visible
End Function

To get the result you want, use

=--SheetVisible(Sheet5!A1)

Alternatively, you could rewrite the UDF to return a 1 or 0, but I
prefer to return boolean values as booleans.

Using a range is better than using a worksheet name, since XL will keep
track of the sheet name if it's changed by the user.

Also, this function will not change until a calculation is triggered on
the sheet with the formula, so you may need to hit F9 to get it to
return the correct value.

 

[Juchelka]

Thank you guys! That was all I needed. After realizing there is no way
to do it just with formulas (SUBTOTAL would kinda work for me, if it
would also operate on rows, instead just the columns...) I spent few
hours on Friday and did what I needed to do in VB. I am cleaning it up
now and one thing I will try to do to get rid of specific references...
Thanks JE!

Alex