VBA code to hide and unhide sheets in a workbook with over 20 sheets

[tbrynard01]

Hi,

I have a sheet that has a bunch of sheets, created a menu sheet with a drop down to unhide all but the specific sheet, but its not working correctly, this is the code on a test one that I created as a practice workbook - I'm not sure where I'm going wrong. It works if I only have one sheet. Or is there a better way to do this.

I have a drop down on the Menu Worksheet that lists all the sheets in the workbook and a table with the sheet names in another worksheet that i keep hidden. This Excel knowledge of users that will use that file is not high so am trying to make it as easy as possible.

Thank you.

Private Sub Worksheet_Change(ByVal Target As Range)

If [MenuOption] = "Sheet 1" Then
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Else
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
End If


If [MenuOption] = "Sheet 2" Then
Sheets("Sheet1").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Else
Sheets("Sheet1").Visible = True
Sheets("Sheet3").Visible = True
Sheets("Sheet4").Visible = True
End If


If [MenuOption] = "Sheet 3" Then
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet4").Visible = False
Else
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet4").Visible = True
End If


If [MenuOption] = "Sheet 4" Then
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Else
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = True
Sheets("Sheet3").Visible = True
End If


Sheets("Menu").Select
Cells(2, 1).Select


End Sub

 

[Fluff]

Do you have a named range called "MenuOption", if so is it 1 cell or a range of cells?
Also is the "Dropdown" data validation?

 

[tbrynard01]

Yes MenuOption is a cell name where the result of the datavalidation dropdown is displayed

 

[Fluff]

Ok, make sure that the names in the Dv exactly match your sheet names & try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ws As Worksheet
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("MenuOption")) Is Nothing Then
      For Each Ws In Worksheets
         If Ws.Name <> Me.Name Then Ws.Visible = Target.Value = Ws.Name
      Next Ws
   End If
End Sub

 

[tbrynard01]

Thank you, not sure what the Dv is? Also do I duplicate the If Ws.(I enter the name of each worksheet) for each worksheet?

 

[tbrynard01]

I'm not sure which values I need to change and how to list them? Thanks

 

[Fluff]

Dv is the data validation.
No you don't need to modify the code, just make sure that the values in the data vaildation drop down are an exact match to your sheet names

 

[tbrynard01]

Thank you so much that's amazing!

 

[Fluff]

You're welcome & thanks for the feedback