Hiding worksheets based on data validation cell field

[thatlem]

I am planning a file with about 30 worksheets, 3 for 10 different departments. To make it easier for staff, I would like to create a data validation list of the 10 departments and use this to drive what worksheets are visible or hidden.

The simple version is below:

Sub Worksheet_calcuation()
IF Sheets("Admin").b1="DEN" Then
Sheets("DEN Summary").Visible = True
Sheets("ENT Summary").Visible = False
Else
Sheets("DEN Summary").Visible = False
Sheets("ENT Summary").Visible = True
End If
End Sub

Where DEN is the selection from the dropdown data validation list on the Admin worksheet.

I get no errors when checking the code, but the file does not correctly hide the specific worksheets. Any suggestions would be appreciated.

thatlem

 

[Brianwarnock]

I cannot believe that you have tried to run this code as it would fail on the first line

IF Sheets("Admin").b1="DEN" Then

which should be

IF Sheets("Admin").Range("b1")="DEN" Then

Brian

 

[thatlem]

Yes, you are correct. Sorry for the memory lapse. However, after the change, the code still does not hide the worksheets as I would expect.

Private Sub Worksheet_Calculate()
If Sheets("Admin").Range("b1") = "DEN" Then
Sheets("Dent Sum").Visible = True
Sheets("ENT Sum").Visible = False
Else
Sheets("Dent Sum").Visible = False
Sheets("ENT Sum").Visible = True
End If
End Sub

thatlem

 

[thatlem]

I got it now, thanks

thatlem

 

[Brianwarnock]

Curious to know what the 2nd problem was.

Brian

 

[thatlem]

Although only an abbreviated version, I figured out that I needed to tell the computer to "look" at the selection in the data validation field. So I added a button, and substituted the Private Sub command. So after making the selection, they hit the "Go" button and it triggers the code to run. Works like a charm.

The final version will include 10 selections and 30 worksheets, so will be much expanded. Is there a way to state in code to hide all worksheets? By adding this command before selecting only those I want visible, it might make the overall code much shorter.


Private Sub CommandButton1_Click()
If Sheets("Admin").Range("b1") = "DEN" Then
Sheets("Dent Sum").Visible = True
Sheets("ENT Sum").Visible = False
Else
Sheets("Dent Sum").Visible = False
Sheets("ENT Sum").Visible = True
End If
End Sub

Thanks
thatlem

 

[Brianwarnock]

Yes and No to hiding all sheets in that you must have at least 1 visible sheet, as you would want the admin sheet visible that is not a prioblem.
In the Thisworkbook objrct select workbook open and use the code below

Private Sub Workbook_Open()
For Each sh In Sheets

If sh.Name <> "Admin" Then
sh.Visible = False
End If
Next sh

End Sub

Brian

 

[thatlem]

Great!

That's exactly what I need to see. Works great.

Thanks
thatlem