I have a spreadsheet that hides other sheets depending on the value of one of my data validations, however I can't work out how to adjust my code currently written to keep the costing sheet from hiding every time I adjust the data validation (other than naming it with all the possible outcomes of my data validation). The code is below:
On the Worksheet it is:
And then the general declarations are:
On the Worksheet it is:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("SelectType").Address Then
Select Case Target.Value
Case "Admin Lists"
ShowAllSheets
Case ""
'do nothing
Case Else
ShowSelSheets
End Select
End If
End Sub
And then the general declarations are:
Code:
Option Explicit
Sub ShowAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Visible = xlSheetVisible
Next ws
End Sub
Sub ShowSelSheets()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Chiller Selection").Range("SelectType").Value
For Each ws In ActiveWorkbook.Sheets
If InStr(1, ws.Name, strType) > 0 Then
ws.Visible = xlSheetVisible
Else
If ws.Name <> "Chiller Selection" Then
ws.Visible = xlSheetHidden
End If
End If
Next ws
End Sub
Last edited by a moderator: