Hide/Unhide Sheets Based On Cell Value

• I have a series of sheets hidden in a workbook. (example sheets named a, b and c).

I want to be able to unhide a sheet based on a Cell "A1" entry on sheet D

So if A is entered, sheet A would unhide, and B and C would remain hidden.

• Re: Unhide Sheet Based On Cell Value Contents

You could use this, it will unhide the shet specified In A1 and hide the others. Place the code in worksheet D.

Files

• Re: Unhide Sheet Based On Cell Value Contents

ok...my example was a little generic, I apologize. Is there a way to make this a loop as opposed to a series of "Case" statements?

I actually have about 30 sheets.

• Re: Unhide Sheet Based On Cell Value Contents

Try this one then.

Files

• Re: Unhide Sheet Based On Cell Value Contents

Thank you so much!!! I was so close...had small typo in the If statement...thanks again!!!

• Re: Hide/Unhide Sheets Based On Cell Value

How would you convert the 'case' code if A1 had a formula (vlookup) to return "A", "B" or "C"?

• Re: Unhide Sheet Based On Cell Value Contents

Hi! This is awesome, almost exactly what I'm was trying to do but I'm trying to be cable to make this work with a column of vaults like having column A in sheet D determine if the sheets are visible then have it go like if A1=yes then Sheet A is visible, If A2=yes then Sheet B is visible, so on and so forth.Or maybe even have the yes no part as a drop down menu. I'm still trying to learn this programming stuff so if you could help me and explain how the code works that would be much appreciated.

• Re: Unhide Sheet Based On Cell Value Contents

Bryce,

Your hide code works perfectly for what I needed, I made a minor change so it is a YES/NO question basically and it worked well.

I want to add a second set of logic applying to anther cell how do I do put this into the VBA. I tried adding a second copy of the code with a different Range Cell but it didn't work after I did that.

Sorry for the rookie question I have zero VBA experience. likely I made a foolish error.

Cheers
Kent

Private Sub worksheet_change(ByVal target As Excel.Range)
Select Case Worksheets("D").Range("A1").Value
Case "YES"
Worksheets("A").Visible = True
Worksheets("B").Visible = False
Worksheets("C").Visible = False
Case ""
Worksheets("B").Visible = False
Worksheets("A").Visible = False
Worksheets("C").Visible = False

End Select
End Sub

Private Sub worksheet_change(ByVal target As Excel.Range)
Select Case Worksheets("D").Range("B1").Value
Case "YES"
Worksheets("A").Visible = False
Worksheets("B").Visible = True
Worksheets("C").Visible = False
Case ""
Worksheets("B").Visible = False
Worksheets("A").Visible = False
Worksheets("C").Visible = False

End Select
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!