Hiding Worksheets based on cell input???

[rwasson23]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am currently using Excel: Mac 2008, which does not support VBA.

Thus, I am trying to come up with a function to hide certain worksheets given the value of a single cell on the main input sheet. I have made several unsuccessful attempts using the IF function? ANY HELP would be greatly appreciated.

Please note: I suspect that I may need to use AppleScripts, of which I have no experience (yet). Not sure how to create the script, nor how to embed it in the workbook.

Thanks, Rich

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am currently using Excel: Mac 2008, which does not support VBA.

Thus, I am trying to come up with a function to hide certain worksheets given
the value of a single cell on the main input sheet. I have made several
unsuccessful attempts using the IF function? ANY HELP would be greatly
appreciated.

Please note: I suspect that I may need to use AppleScripts, of which I have no
experience (yet). Not sure how to create the script, nor how to embed it in
the workbook.

Thanks, Rich

Well, there are a bunch of problems with your expectations. First, no matter
what the version or platform of Excel, this can NOT be done with a function.
Functions can only return values to the cell where they exist. The can not
change the Excel environment. So give up on using an If statement, it can't
do what you are trying to do.

Second, even if you manage to do this in Applescript, Applescripts can NOT
be embedded in a workbook. And, Applescripts can not be activated from a
change in cell contents.

So, using Excel 2008, the only alternative to accomplishing what you want is
to use an XLM macro. The good news is that this technique should work fine,
and will be forwards and backwards compatible through all Excel versions and
platforms. The XLM macro will be an integral part of the workbook and will
be embedded within it.

It seems like a simple and fun macro, so if you give me the following info,
I'll prepare it for you:
-Name of the "main input sheet"
-Cell address on this sheet that governs which sheets become visible
-Cell Value1 sheet names to be visible
-cell value2 sheet names to be visible
-etc. As needed

 

[rwasson23]

Bob, many thanks for the help! I really appreciate your time and instruction. That said, here you go....

First, the workbook contains 11 sheets, with the following desired visibility characteristics:

Instructions - Always visible
Input - Always visible
Bristol DigiTint - cell dependent
Bristol DigiShield - cell dependent
Bristol Combined - cell dependent
Talladega DigiTint - cell dependent
Talladega Combined - cell dependent
LeMans DigiTint - cell dependent
LeMans DigiShield - cell dependent
LeMans Combined - cell dependent
Pricing & Fees - Always hidden, manually controlled via Format menu

Name of the "main input sheet":
Input

Cell address on this sheet that governs which sheets become visible:
G68

Cell Value1 sheet names to be visible:
1 - Bristol DigiTint, Bristol DigiShield, Bristol Combined

Cell value2..5 sheet names to be visible:
2,3,4,5 -Talladega DigiTint, Talladega Combined

Cell value6...9 sheet names to be visible:
6,7,8,9 -LeMans DigiTint, LeMans DigiShield, LeMans Combined

I hope I have answered the questions correctly, please let me know if not or if more information is needed. Again, thanks so much for the help!

 

[JE McGimpsey]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am currently using Excel: Mac 2008, which does not support VBA.

Thus, I am trying to come up with a function to hide certain worksheets given
the value of a single cell on the main input sheet. I have made several
unsuccessful attempts using the IF function? ANY HELP would be greatly
appreciated.

Functions can only return values to their calling cells. They can't
affect environmental properties such as visibility, formatting, etc.

Please note: I suspect that I may need to use AppleScripts, of which I have
no experience (yet). Not sure how to create the script, nor how to embed it
in the workbook.

Applescripts would need to be run manually, rather than automatically.
It may be possible to do what you want with an XL4M (old-style) macro.

If you can be more specific about what you'd like to see your formula
doing, you might be able to get some better advice here.

 

[Bob Greenblatt]

Bob, many thanks for the help! I really appreciate your time and instruction.
That said, here you go....

First, the workbook contains 11 sheets, with the following desired visibility
characteristics:

Instructions - Always visible
Input - Always visible
Bristol DigiTint - cell dependent
Bristol DigiShield - cell dependent
Bristol Combined - cell dependent
Talladega DigiTint - cell dependent
Talladega Combined - cell dependent
LeMans DigiTint - cell dependent
LeMans DigiShield - cell dependent
LeMans Combined - cell dependent
Pricing & Fees - Always hidden, manually controlled via Format menu

Name of the "main input sheet":
Input

Cell address on this sheet that governs which sheets become visible:
G68

Cell Value1 sheet names to be visible:
1 - Bristol DigiTint, Bristol DigiShield, Bristol Combined

Cell value2..5 sheet names to be visible:
2,3,4,5 -Talladega DigiTint, Talladega Combined

Cell value6...9 sheet names to be visible:
6,7,8,9 -LeMans DigiTint, LeMans DigiShield, LeMans Combined

I hope I have answered the questions correctly, please let me know if not or
if more information is needed. Again, thanks so much for the help!

What is a good email address to use to send the sample macro?

 

[rwasson23]

What is a good email address to use to send the sample macro?Bob,
Please use: (e-mail address removed)

Thanks,
Rich