Hide/Unhide sheets based on text result of IF Formula in cell in another sheet

Hello,

I have a workbook with four sheets: "Main", "OutriggersSM", "OutriggersLG", and "Ref". Forum5.xlsm There are a number of different option combinations on Main, determined using combos of Y/N in cells B15, B16, E15, & E16. Depending on what the user selects on Main, Ref cell A16 will show one of four letter codes. Based on the code in A16, cell A17 will then show one of three options: blank or the text SMALL KIT or LARGE KIT. A16 and A17 function using IF formulas. (yes, I know it's a lot of IFs, sorry :D)

If A17 shows blank, I want both the OutriggersSM & Outriggers LG sheets to be visible. If A17 shows SMALL KIT, I only want the user to see the OutriggersSM sheet (hide OutriggersLG). If A17 shows LARGE KIT, have OutriggersLG visible and OutriggersSM hidden.

There's a lot of solutions posted online but I haven't been able to get any to work. Here's my current code in the Ref worksheet:

Please Login or Register  to view this content.

Also, started simple with nested IFs but obviously I should be doing this with an If-Else-Else... I'll get that figured out later.

Thank you in advance!

this is one way of doing it with the SELECT CASE method:

Please Login or Register  to view this content.

Here if how to make your IF work:

Please Login or Register  to view this content.

That's not a valid Worksheet Change event subroutine header. It should have a Target range reference. You use that to monitor the cell that changes, in this case, cells on the Main sheet ... So that's where your (corrected) change event needs to be. A change event is NOT triggered by a change in a formula output.

I'd start by hiding all the sheets and then unhiding the relevant sheets depending on the value in A17. Make each IF independent and have an Exit Sub before the End If.


Regards, TMS

@Pierre: if the Change event is on the Ref sheet it won't work because A17 is a formula based on other cells.

Regards, TMS