Hide/show multiple sheets on cell value change

[Cflaursen]

Hi all

I have a sheet ("Setup") in which I have a switch (as a drill down/validation list) betwee "CTR" and "Clarity". When value of cell BD2 = "CTR" I want some sheets to be shown and when I change value to "Clarity" I want other sheets to be shown.

I have already implemented a Auto_open sub, but now need one for when the change is done while the workbook is open. I have written the following macro in a module:


Sub Worksheet_Change(ByVal Target As Range)

'Macro to hide either Clarity or CTR related sheets

If Target.Value = "CTR" Then
Sheets("Utilization Data - Clarity").Visible = xlVeryHidden
Sheets("Utilization Pivot - Clarity").Visible = xlVeryHidden
Sheets("Utilization Charts - Clarity").Visible = xlVeryHidden
Sheets("Planned vs Actuals Charts - Cla").Visible = xlVeryHidden
Sheets("Planned vs Actuals Pivot - Clar").Visible = xlVeryHidden
Sheets("Utilization Data - CTR").Visible = True
Sheets("Utilization Pivot - CTR").Visible = True
Sheets("Utilization Charts - CTR").Visible = True
Sheets("Planned vs Actuals Pivot - CTR").Visible = True
Sheets("Planned vs Actuals Charts - CTR").Visible = True

ElseIf Target.Value = "Clarity" Then
Sheets("Utilization Data - CTR").Visible = xlVeryHidden
Sheets("Utilization Pivot - CTR").Visible = xlVeryHidden
Sheets("Utilization Charts - CTR").Visible = xlVeryHidden
Sheets("Planned vs Actuals Pivot - CTR").Visible = xlVeryHidden
Sheets("Planned vs Actuals Charts - CTR").Visible = xlVeryHidden
Sheets("Utilization Data - Clarity").Visible = True
Sheets("Utilization Pivot - Clarity").Visible = True
Sheets("Utilization Charts - Clarity").Visible = True
Sheets("Planned vs Actuals Charts - Cla").Visible = True
Sheets("Planned vs Actuals Pivot - Clar").Visible = True

Else
Exit Sub
End If

End Sub


However I can't even "step into" in Debug mode. Can anyone provide me with som guidence?

Br, CFL

 

[Trevor G]

Try running the code in a module in the workbook, rather than behind the sheet. You can then test it and step through the code. Just specify the sheet name and cell reference to start with in your code.

 

[Cflaursen]

Hi Trevor G

Can't quite figure out what you mean.

If my depending cell is in worksheet("Setup").Range(BD2) could you then examplify how my macro should look?

Br, Chris

 

[Trevor G]

Chris, try this. in your workbook use ALt + F11 (that takes you into VBA) then use the Insert Menu and select Module, then paste this code in. If it works based on the cell value in the sheet then you just need to view the workbook, if there is an issue you can post back with the debug line

Sub CheckCell()
Sheets("Setup").Activate
If Range("BD2").Value = "CTR" Then
Sheets("Utilization Data - Clarity").Visible = xlVeryHidden
Sheets("Utilization Pivot - Clarity").Visible = xlVeryHidden
Sheets("Utilization Charts - Clarity").Visible = xlVeryHidden
Sheets("Planned vs Actuals Charts - Cla").Visible = xlVeryHidden
Sheets("Planned vs Actuals Pivot - Clar").Visible = xlVeryHidden
Sheets("Utilization Data - CTR").Visible = True
Sheets("Utilization Pivot - CTR").Visible = True
Sheets("Utilization Charts - CTR").Visible = True
Sheets("Planned vs Actuals Pivot - CTR").Visible = True
Sheets("Planned vs Actuals Charts - CTR").Visible = True
ElseIf Range("BD2").Value = "Clarity" Then
Sheets("Utilization Data - CTR").Visible = xlVeryHidden
Sheets("Utilization Pivot - CTR").Visible = xlVeryHidden
Sheets("Utilization Charts - CTR").Visible = xlVeryHidden
Sheets("Planned vs Actuals Pivot - CTR").Visible = xlVeryHidden
Sheets("Planned vs Actuals Charts - CTR").Visible = xlVeryHidden
Sheets("Utilization Data - Clarity").Visible = True
Sheets("Utilization Pivot - Clarity").Visible = True
Sheets("Utilization Charts - Clarity").Visible = True
Sheets("Planned vs Actuals Charts - Cla").Visible = True
Sheets("Planned vs Actuals Pivot - Clar").Visible = True
Else
Exit Sub
End If
End Sub

 

[Cflaursen]

Hi Trevor

The macro seems to work when i run it in debug mode. However I need the "sheet hide" and "sheet show" to take place automatically then the cell value is changed - it doesn't do that currently. Can I somehow do this - maybe by changing the macro type?

Br, Chris