Hide / Show worksheets Based on Multiple Criteria

[miconian]

Hello.

Someone on mrexcel.com, I think Nimrod, gave me the following code:

Select Case Sh.Name
 Case "apple sheet": ColName = "apple"
 Case "orange sheet": ColName = "orange"
 Case "grape sheet": ColName = "grape"
 Case "pear sheet": ColName = "pear"
 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

The idea was this: in column A, the user will enter words corresponding to several worksheets (same workbook). When any given worksheet is indicated by an item in column A, then that worksheet should appear. If there is no corresponding text in column A, then that worksheet should not appear. For example, if the word "apple" is entered into any cell in column A, then the apple worksheet will become visible. If that same cell is overwritten, and there are no other incidents of "apple" in column A, then the apple sheet will disappear.

The code works great. The problem is this. There is one renegade worksheet that corresponds to any of nine different items that might be entered into column A. For clarity, let's call it the "vegetable" worksheet, and say that it ought to become visible if, and only if, any of the following appear in column A: carrots, celery, onion, sprout, lettuce, broccoli, asparagus, cale, or turnip.

Now, maybe I was taking the wrong approach, but I had an idea that I could simplify this problem by creating a "vegetable verification" column, column R. If the name of a vegetable appears in column A, then the word "vegetable" appears in column R. Otherwise, it doesn't. Obviously, then, if the vegetable verification column contains even one instance of "vegetable," then the vegetable worksheet should be visible (and otherwise, it shouldn't).

However, I cannot figure out how to control the visibility of the vegetable worksheet as appropriate, and at the same time still use the code above for the other worksheets. That code will cycle through the sheets, find the vegetable sheet, see that there is no corresponding entry in column A, and make the vegetable sheet invisible, whether it should be or not.

I tried doubling the second part of the code to make it check both column A and the vegetable verification column:

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
 
 With Columns("R:R")
 Set c = .Find(ColName, LookIn:=xlValues)
 If Not c Is Nothing Then
 Sh.Visible = xlSheetVisible
 Else
 Sh.Visible = xlSheetHidden
 End If
 End With

...but that doesn't work; the second instance of "Sh.Visible=xlSheetHidden" will hide all the fruit worksheets.

So, any suggestions would be greatly appreciated, and I would be glad to provide any additional info that may be necessary or helpful.

Thanks.

[Jacob Hilderbrand]

Would this work?

With Columns("R:R") 
Set c = .Find(What:="Vegetable", LookIn:=xlValues) 
If Not c Is Nothing Then 
Sheets("Vegetable").Visible = xlSheetVisible 
Else 
Sheets("Vegetable").Visible = xlSheetHidden 
End If 
End With

[miconian]

That does make the vegetable sheet appear at the right time. But it does not make the vegetable sheet invisible when there are no occurances of "vegetable" in column R. Perhaps this has something to do with the fact that column R is updated by code, whereas column A is updated by the user...?

Thanks

[Richie(UK)]

Hi miconian,

OK, having looked at the thread where Nimrod helped you out (link added for reference) I believe that the following should help. http://www.mrexcel.com/board2/viewto...er=asc&start=0

Worksheet event code (for the 'Main' sheet):

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

In a general module:

Option Compare Text

Public Sub CheckSheets()
    Dim Sh As Worksheet, ColName As String, c As Range
For Each Sh In Worksheets
        If Not Sh.Name = Sheet1.Name Then
Select Case Sh.Name
                Case "apple sheet": ColName = "apples"
                Case "pear sheet": ColName = "pears"
                Case "orange sheet": ColName = "oranges"
                Case "banana sheet": ColName = "bananas"
                Case "vegetable sheet": ColName = "vegetable"
                Case Else: ColName = Sh.Name
            End Select
With Sheet1
                If Not ColName = "vegetable" Then
                    Set c = .Columns("A:A").Find(ColName, LookIn:=xlValues)
                Else
                    Set c = .Columns("R:R").Find(ColName, LookIn:=xlValues)
                End If
                If Not c Is Nothing Then
                    Sh.Visible = xlSheetVisible
                Else
                    Sh.Visible = xlSheetHidden
                End If
            End With
End If
    Next Sh
End Sub

A great feature of this site is that we can also add attachments. I've attached a workbook that incorporates the above.

HTH

PS: I've only entered the column R formula as far as row 20 - amend to suit.

EDIT : Attachment amended 4.06PM 19Sep04 (thanks MD)

[mdmackillop]

An alternative method, utilising only Column A, if this is preferred, based upon Ritchie's zip file.
MD

BTW Ritchie, I get run-time error 9 (Subscript out of range) in this routine (Excel2000)

[vba]Private Sub Workbook_Open()
Sheets("X").ScrollArea = "A1:H45"
End Sub[/vba]

[mdmackillop]

If you want to keep the check list on the sheet, this can be done with a small change in the CheckVeg sub; Veg = Range("Vegs").Value, where "Vegs" is a named range in the workbook

[Richie(UK)]

BTW Ritchie, I get run-time error 9 (Subscript out of range) in this routine (Excel2000)

Private Sub Workbook_Open()
Sheets("X").ScrollArea = "A1:H45"
End Sub

Oops - left over code from helping somebody else! Now removed - thanks MD

[miconian]

Richie: Your code seems to work great, although to make it work, I find that I need to call Checksheets from worksheet_change as well.

One question: Why does Checksheets go in a module, and not in the sheet code? Could it? Maybe it's because it refers to multiple sheets?

mdmackillop: While I don't *need* to use column R in this case, I might as well since I use it for other calculations (e.g. a sum of all numbers in column M on vegetable rows). But I will read your code for the sake of my edification.

Thank you both very much for putting so much time into this.

[Richie(UK)]

Richie: Your code seems to work great, although to make it work, I find that I need to call Checksheets from worksheet_change as well.

One question: Why does Checksheets go in a module, and not in the sheet code? Could it? Maybe it's because it refers to multiple sheets?

...

Thank you both very much for putting so much time into this.

Hi miconian,

Glad it worked, and I'm pleased to have been able to help. I'm a little puzzled by the statement that to make it work you need to call the routine from the Change event as well - both the example code I posted and the example workbook already contain the Change event.

The CheckSheets routine is in a general module because it is a general routine, rather than an event-driven routine such as those found in the ThisWorkbook and Sheet objects. I generally consider it good-housekeeping, for want of a better description, to keep event code seperate from general code. That said, you could add it to the Sheet code window if you so wished.

HTH

[miconian]

Richie,

Right you are...I meant Worksheet_Calculate. If I don't call Checksheets from there as well, the hiding and revealing of sheets is not reliably correct.

Thanks for clarifying on the module vs. worksheet issue.

[Richie(UK)]

Hi miconian,

Richie,

Right you are...I meant Worksheet_Calculate. If I don't call Checksheets from there as well, the hiding and revealing of sheets is not reliably correct.

I don't see why this should be so. The only calculation of relevance is of the 'Vegetable' formulas in column R - these depend directly on a change taking place in column A. There should, therefore, be no need to also call the routine from the Calculate event - this just adds unnecessary overhead to the workbook. Depending on the nature of the workbook in question this may not be an issue that concerns you.

The only reason I can think of, without more detail, that may explain this is if you are using Excel97 and using Data Validation lists to select the entries in column A (which, in that version, didn't fire the Change event).

Thanks for clarifying on the module vs. worksheet issue.

No problem.

[miconian]

Hi Richie,

The workbook in question has a lot of code in it now; there is probably something going on that I don't know about. Anyway, I'm fine with using the calculate event for now. Maybe in the future I'll figure out why it seems to be necessary and can stop using it for this purpose.

[miconian]

Regarding the calculate vs change issue...maybe it's relevant that the change in column A takes place through a validation list? It seems to me that maybe changing the value of a cell via a validation list doesn't trigger the worksheet_change event, but it does trigger the worksheet_calculate event? Possibly? I tried taking checksheets out of worksheet_calculate, and the desired result no longer occurs.

[Anne Troy]

What version are you using, miconian?

Note to MD & Richie:
Damn, you guys are impressive.
I love this stuff!!

[miconian]

Dreamboat,

I'm using Excel 2002 with SP-2.

Caught you on the radio the other day, you were slick.

[Anne Troy]

Gee.

Thanks!

[miconian]

I feel fairly sure at this point that this is indeed a validation issue.

As an experiment, I removed the Checksheets call from worksheet_calculate, put it in worksheet_change, selected the entire worksheet, turned off validation, and made a change in column A that ought to result in Checksheets being called and one of the worksheets becoming hidden.

It worked.

But I don't think that this means that the worksheet_change event never responds to a change in a cell made through a validation list. I also have code in my worksheet_change event like this:

' Put current date in column Q every time a change is made to a given row
  ' First make sure that deleting the whole row will still be allowed
          If Target.Cells.Count > 1 Then
              Exit Sub
          End If
  'Now make sure we're not about to write to the header row
  If Target.Row <> 2 Then
'Now put current date in 'date of last change' column with every change to a given row
      Range("Q" & Target.Row).Value = Date
End If

...and that routine goes into action when I make a change using a validation list.

So I don't know what the deal is...

[miconian]

For what it's worth, I am now even more sure about this calculate/change issue than before. I can call Checksheets from the beginning of my Worksheet_Change routine, and it will not do its job when I change a cell in column 1, but as soon as I call it from Worksheet_Calculate, it does what it's supposed to do. Maybe selecting from a validation list really doesn't count as a change.

I experimented with this by leaving the call in worksheet_change, and adding data to a cell that doesn't use validation. The appropriate sheet was un-hidden...but only after about 20 seconds of flicker. (and that's whether I have screenupdating set to false or not).