hide or unhide worksheets based on cells in another sheet

Re: hide or unhide worksheets based on cells in another shee

This example assumes your "Fruit Column" is ColumnA

This code must be copied into the code window of the Sheet you want it to work from... not a common module .

Private Sub Worksheet_Activate()
CheckSheets
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
CheckSheets
End If
End Sub


Public Sub CheckSheets()
Dim Sh As Worksheet
For Each Sh In Worksheets
If Not Sh.Name = ActiveSheet.Name Then

With Columns("A:A")
Set c = .Find(Sh.Name, LookIn:=xlValues)
If Not c Is Nothing Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetHidden
End If
End With

End If
Next Sh
End Sub


TO INSTALL IN SHEET CODE WINDOW:
1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

Re: hide or unhide worksheets based on cells in another shee

Hey, thanks for this. I didn't get around to trying it until now.

One question. What if it turns out that I want to name the sheets that correspond to the column A values a little bit differently than the actual values? For example, the sheet that becomes visible when there is a cell in column A of the main sheet containing "apples." What if I don't want to call the related sheet "apples," but rather, "apple sheet"?

Many thanks!

Re: hide or unhide worksheets based on cells in another shee

The code in the sheet doesn't change but this does ...

the option compare text MUST be the first line of code at the TOP of the code module. And be outside the macro .



Option Compare Text

Public Sub CheckSheets()
Dim Sh As Worksheet
For Each Sh In Worksheets
If Not Sh.Name = ActiveSheet.Name Then

Select Case Sh.Name
Case "apple sheet": ColName = "apples"
Case "pear sheet": ColName = "pears"
Case Else
ColName = Sh.Name
End Select

With Columns("A:A")
Set c = .Find(ColName, LookIn:=xlValues)
If Not c Is Nothing Then
Sh.Visible = xlSheetVisible
Else
Sh.Visible = xlSheetHidden
End If
End With

End If
Next Sh
End Sub

Re: hide or unhide worksheets based on cells in another shee

So this can/should be in a module outside the sheet?

Also, variables ColName and c are not defined. I figure they're both strings?

Re: hide or unhide worksheets based on cells in another shee

Just replace the old Sub CheckSheets with the new one. The 2 undeclared variables will be variants by default. If you read my code you will see I do this alot. Most people hear will tell you it's a bad habit . I won't disagree with them :wink: . There are more reasons to declare all your variables then there are for not declaring them.

Re: hide or unhide worksheets based on cells in another shee

I have option explicit set for my own benefit while I learn, though...

Sorry, haven't had time to test this yet. Very busy and very tired...but I'll get to it soon.

Re: hide or unhide worksheets based on cells in another shee

It seems to work pretty well. But what I'd like to do is put it in, say, worksheet_calculate instead of worksheet_activate. Will I be begging for much slower performance if I do this? The issue is that, with the code the way it is, you have to actually select a different worksheet in order to cause the extra sheets to appear or disappear. But the effect I want is for them to magically appear or disappear as soon as a corresponding entry is written to a cell in column A of the main sheet.

Re: hide or unhide worksheets based on cells in another shee

...of course, I can just try it myself, and I did. Seems to work fine in worksheet_calculate. Is there a reason not to do it that way?