Multiple Case Statement Options

[jUStPunkin]

All,

I have this code I've written up. It works fine. But, I want to embellish on it a bit. I have Cell J16, which is a Y/N selection. I have Cell J17, which is a numerical list. What I want to happen is to select If Cell J16 = Yes, and Cell J17 equals a certain number, certain sheets are hidden and certain ones are visible.

Is it as simple as adding an AND statment after the Case Is = "Y" ?

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Worksheets("Input").Range("$J$16")
Case Is = "Y"
Sheets("Spouts").Visible = False
Sheets("Redistribution Calculations").Visible = False
Sheets("5 Downcomer~4 Trough").Visible = True
Sheets("6 Downcomer~4 Trough").Visible = True
Sheets("8 Downcomer~4 Trough").Visible = True
Sheets("8 Downcomer~6 Trough").Visible = True
Sheets("10 Downcomer~4 Trough").Visible = True
Sheets("10 Downcomer~6 Trough").Visible = True
Sheets("12 Downcomer~4 Trough").Visible = True
Sheets("12 Downcomer~6 Trough").Visible = True
Case Is = "N"
Sheets("Spouts").Visible = True
Sheets("Redistribution Calculations").Visible = True
Sheets("5 Downcomer~4 Trough").Visible = False
Sheets("6 Downcomer~4 Trough").Visible = False
Sheets("8 Downcomer~4 Trough").Visible = False
Sheets("8 Downcomer~6 Trough").Visible = False
Sheets("10 Downcomer~4 Trough").Visible = False
Sheets("10 Downcomer~6 Trough").Visible = False
Sheets("12 Downcomer~4 Trough").Visible = False
Sheets("12 Downcomer~6 Trough").Visible = False
End Select
End Sub

 

[offthelip]

Your code is over complicated as it is you don't need a case statement at all for the simple case. this code does the same thing

Private Sub Worksheet_Change(ByVal Target As Range)
TFLAG = Worksheets("Input").Range("$J$16") = "Y"
Sheets("Spouts").Visible = Not (TFLAG)
Sheets("Redistribution Calculations").Visible = Not (TFLAG)
Sheets("5 Downcomer~4 Trough").Visible = TFLAG
Sheets("6 Downcomer~4 Trough").Visible = TFLAG
Sheets("8 Downcomer~4 Trough").Visible = TFLAG
Sheets("8 Downcomer~6 Trough").Visible = TFLAG
Sheets("10 Downcomer~4 Trough").Visible = TFLAG
Sheets("10 Downcomer~6 Trough").Visible = TFLAG
Sheets("12 Downcomer~4 Trough").Visible = TFLAG
Sheets("12 Downcomer~6 Trough").Visible = TFLAG
End Sub

Starting from this , how you add in an addition number condition depend on how many different combinations you are looking at and also whether there is any pattern to which sheets are visible and which aren't

 

[jUStPunkin]

Your code is over complicated as it is you don't need a case statement at all for the simple case. this code does the same thing

Private Sub Worksheet_Change(ByVal Target As Range)
TFLAG = Worksheets("Input").Range("$J$16") = "Y"
Sheets("Spouts").Visible = Not (TFLAG)
Sheets("Redistribution Calculations").Visible = Not (TFLAG)
Sheets("5 Downcomer~4 Trough").Visible = TFLAG
Sheets("6 Downcomer~4 Trough").Visible = TFLAG
Sheets("8 Downcomer~4 Trough").Visible = TFLAG
Sheets("8 Downcomer~6 Trough").Visible = TFLAG
Sheets("10 Downcomer~4 Trough").Visible = TFLAG
Sheets("10 Downcomer~6 Trough").Visible = TFLAG
Sheets("12 Downcomer~4 Trough").Visible = TFLAG
Sheets("12 Downcomer~6 Trough").Visible = TFLAG
End Sub

Starting from this , how you add in an addition number condition depend on how many different combinations you are looking at and also whether there is any pattern to which sheets are visible and which aren't

OK, thanks for that.

I have two more steps. The first is, if that first flag is Y, then we enter a number - based on a data validated drop down (5, 6, 8, 10 or 12), and it will turn off the sheets that aren't applicable, which will be all but one or two of the sheets shown. So, if they select 6, it will turn off the 5, 8, 10 and 12 downcomer sheets).

Also, if that first flag is N, it's going to turn off all the sheets except spouts and redistribution.

Then, I also have another drop down, Y or N, which will turn on or off redistribution. But that is only applicable if J16 is N

Make sense? I'd really like to understand how this is done as well.

Thanks!

 

[offthelip]

The way I would do this is to use two dimensional array to define which sheets are visible and which sheets aren't for each selected drop down number. The easy way to set this up is to use a hidden worksheet, in which case you load it. however I have shown you here how to avoid that by initalising a 2D array. Then it is a simple case of a loop to find which number is selected and then copy the visible flags to the worksheet using the worksheet index, I have also used the TFLAG you saw before to initialise the array depending on the selection of Y or N. This method is very flexible because it copes with more sheets or more drop down numbers or more conditions very easily.
I have written it as standalone subroutine with a debug flag to allow you to test it, when you have got the setting in the ARRAY5 to ARRAY12 correct just set DEBUGFLAG=FALSE at the top
I suggest that only then move it to the worksheet change event

Sub test()
debugflag = True
Dim shtflags(1 To 5, 1 To 11) As Variant ' This array has 5 "rows" which relate to the 5 drop downs
                                          'and 11 "columns" which relates to the 10 workhseets in the workbook plus one for the drop down number( assumed!!)
TFLAG = Worksheets("Input").Range("$J$16") = "Y"

Dim array5, arra6, array8, array10, array12
array5 = Array(5, not(TFLAG), Not( TFLAG), False, TFLAG, False, TFLAG, TFLAG, False, TFLAG, False)  ' set which visiable flags you want set for each drop down number
array6 = Array(6, TFLAG, TFLAG, True, True, False, True, True, False, True, False)
array8 = Array(8, TFLAG, TFLAG, True, True, False, True, False, False, True, False)
array10 = Array(10, TFLAG, TFLAG, False, True, False, True, True, False, True, False)
array12 = Array(12, TFLAG, TFLAG, False, True, False, True, True, False, True, False)
For i = 1 To 11
shtflags(1, i) = array5(i - 1)
shtflags(2, i) = array6(i - 1)
shtflags(3, i) = array8(i - 1)
shtflags(4, i) = array10(i - 1)
shtflags(5, i) = array12(i - 1)
Next i
' this all just creates a 2 dimensional array with a number and a set of true/ false flags to turn worksheets off
DDno = Worksheets("Input").Range("$J$17")
If DDno > 0 Then
For j = 1 To UBound(shtflags, 1)
   If DDno = shtflags(j, 1) Then
    For i = 2 To 11
     If debugflag Then
     MsgBox (Worksheets(i).Name & " visible is set " & shtflags(j, i))
     Else
     Worksheets(i).Visible = shtflags(j, i)
     End If
    Next i
    Exit For
   End If
Next j
End If
End Sub

 

[jUStPunkin]

Thanks. I'm going to give that a try and see if I can make it work! I do have more worksheets in this spreadsheet that aren't affected by any of this (in other words, they are always visible). Do I need to account for those at all? Like in the array of 11 columns (11 worksheets)?

 

[offthelip]

Yes you may have to, it depends on what index number they are, ifthe numbers are greater than the last one you want to turn on and off then you can ignore it. if it is less then you just need to set it TRUE all the time. An alternative would be to check the name of each worksheet against another array in which you store the names of the arrays you want to change. this means initialising another array. I think just setting it TRUE is easier. Obviously if there are more than 10 worksheets you are dealing with you need to change every array and loop that that will affect. e.g shtflags, array5 etc, Do i = 1 to 11, do i = 2 to 11

 

[jUStPunkin]

Yes you may have to, it depends on what index number they are, ifthe numbers are greater than the last one you want to turn on and off then you can ignore it. if it is less then you just need to set it TRUE all the time. An alternative would be to check the name of each worksheet against another array in which you store the names of the arrays you want to change. this means initialising another array. I think just setting it TRUE is easier. Obviously if there are more than 10 worksheets you are dealing with you need to change every array and loop that that will affect. e.g shtflags, array5 etc, Do i = 1 to 11, do i = 2 to 11

Thanks! That's what I was thinking. I'm going to work on this later today (apparently, I have to do "real" work today too. I appreciate the help, and I'm sure I'll be back with more questions (maybe).