"Hide sheet if" macro

[Cflaursen]

Hi all

I have a model where I want to hide certain sheets based on the value in a single formula.

E.g. say I want to hide sheet 1,3,5 if value in cell A1 = "Yes" alternatively I want to hide sheet 2,4,6 if value in cell A1 = "No".

(some sheets will never be nidden - say sheet 7,8)

- Sheets must be "veryHidden" taking effect from load of workbook.

Hope someone can help me out?

Br, Chris

 

[Trevor G]

Try this as an example for a single sheet

Sub Macro1()
If Range("A1") = "Yes" Then
    Sheets("Sheet1").Visible = xlVeryHidden
ElseIf Range("A1") = "No" Then
Sheets("sheet3").Visible = xlVeryHidden
Else
Exit Sub
End If
End Sub

 

[Jameo]

Need some more information I am afraid. Specifically, what reference cells are you using? How many sheets need to be hidden and when? Which sheets are to be included/excluded from the macro? What happens when the value in the cell changes? Do you want the hidden sheets to become visible?

Try and be as specific as possible.

 

[Cflaursen]

Hi Jameo

Modifying my previous example...

Only cell Sheet 1, A2 (reference cell) and whole sheets are relevant.

If Sheet 1, A2 = "Yes" then sheet 2, 4 & 6 should be "veryHidden"
If Sheet 1, A2 = "No" the sheet 3, 5 & 7 should be "veryHidden"

Sheets 8 and 9 should always be visible.

Say workbook is opened with Sheet 1, A2 = "Yes" then only sheet 2, 4 & 6 should be "veryhidden" (on opening). If Sheet 1, A2 is changed to "No" while file is open, then sheet 2, 4 & 6 should become visible while sheet 3, 5 & 7 should become "veryHidden".

I hope this provides needed clarification?

Br, Chris

 

[Cflaursen]

Hi all

First of all thanks for the replies to my post.

I can't quite make it work. As written earlier in this thread I need the macro to run on workbook open and if changes are made when the workbook is already open.

What I have done is to enter the following macro (which depicts the real layout) in "ThisWorkbook".

---

Private Sub Workbook_Open()
'Macro to hide either Clarity or CTR related sheets'
If Sheets("Sheet2").Range("BD2") = "CTR" Then
Sheets("Sheet7").Visible = xlVeryHidden
Sheets("Sheet17").Visible = xlVeryHidden
Sheets("Sheet16").Visible = xlVeryHidden
Sheets("Sheet26").Visible = xlVeryHidden
Sheets("Sheet27").Visible = xlVeryHidden

ElseIf Sheets("Sheet2").Range("BD2") = "Clarity" Then
Sheets("sheet8").Visible = xlVeryHidden
Sheets("sheet19").Visible = xlVeryHidden
Sheets("sheet18").Visible = xlVeryHidden
Sheets("sheet23").Visible = xlVeryHidden
Sheets("sheet22").Visible = xlVeryHidden
Else
Exit Sub
End If
End Sub

---

Br, Chris

 

[Cflaursen]

Hi again all

Now I got indications that my macro i run on Workbook open but I currently get the error "Run-time error '9': Subscript out of range".

Can anyone help me out on this one?

Br, CFL