Hide or unhide worksheets from cell values in sheet 1

**glenin** · 02-18-2009, 08:12 PM

I want to set up a few workbooks with 11 sheets. Sheet 1 will in effect be an index, with True or False values in say cells D5:D14. The True/False values will be generated by form control check boxes in some of the workbooks and by IF(TODAY()>xxxx formulas or similar in other workbooks.

I then want to be able to hide or unhide sheets 2-11 based on the True/False values in cells D5:D14 in sheet 1.

Any guidance or assistance will be appreciated.

**JBeaucaire** · 02-18-2009, 08:29 PM

Not sure where your macros are going to reside, but you can assign a macro to the checkbox.

Here's a basic macro, paste it into a standard module:

Please Login or Register  to view this content.

Right-click on your check-box and ASSIGN MACRO, select this macro. Then edit the macro and add this to title:

Please Login or Register  to view this content.

Adding the "PRIVATE" simply hides the code when others are selecting macros to run manually.

**JBeaucaire** · 02-18-2009, 08:32 PM

If you use THIS version, then the users won't be able to UNHIDE the sheets manually using the Format > Sheets > Unhide.

Please Login or Register  to view this content.

This version can only be unhidden again with a macro, this checkbox, for instance.

**glenin** · 02-18-2009, 09:41 PM

Thanks - one down one to go!

I've just set up a test WB using your xlVeryHidden option and the hide/unhide works fine from the checkbox controls in sheet 1.

Any suggestions on how to achieve the same hide/unhide effect without using a checkbox, but using a true or false formula result in cells in sheet 1?

Example: Cell B5 in sheet 1 contains an IF formula testing whether TODAY() is greater than or equal to say June 30 2009. If the result is FALSE then sheet 2 is to be hidden, if TRUE then sheet 2 is to be unhidden.

Thanks again for your assistance.

**JBeaucaire** · 02-18-2009, 10:32 PM

This should do it:

Please Login or Register  to view this content.

By the way, this goes in the Worksheet module, not a standard module.

**glenin** · 02-19-2009, 12:33 AM

I'm struggling to get this one to go.

After setting up a fresh test xlsm workbook I pasted the code to the sheet 1 module. I originally set up a formula in B5 to return a true or false value based on input at B3. No luck - no hide/unhide sheet 2.

Then tried typing true or false directly into B5 - still no sheet 2 hide/unhide.

Then tried pasting the code to the workbook module and carried out the same two tests as above - still no go. Also tried saving/reopening file but no change.

I'm pretty new to VBA so I've probably missed something simple - any idea what I haven't done or haven't done right?

**JBeaucaire** · 02-19-2009, 01:54 AM

Not sure. Here's a book with it working...

**glenin** · 02-19-2009, 05:41 AM

Thanks for the sample WB - it works fine.

I've check my WB again and again and can't find the difference - I'll have a fresh look tomorrow and let you know when/if I find my error.

Your asistance much appreciated.

**Dropman** · 09-07-2011, 06:00 PM

Hi all,

First off, what I know about VBA would pass through the eye of a needle and not hit the sides.

I have tried to use this code to hide a worksheet and it is not working. I added the code as shown but changed the test value and cell. I also changed the worksheet name to match my names. The code is in a worksheet named SAP_SCOPE and if F50 is FALSE the worksheet Financial_Accounting should not be displayed.

Obviously the code is not being executed but I have no idea why.

Thanks