Additional VBA Help

[jUStPunkin]

All,

I have recieved some help on this prior, but I've been asked to refine the results a little more.
These are the potential combinations.

			Sheet Hidden
G_TwoTiered	G_CapturedAbove	G_FeedType	Spout	Spouts2	Redistribution Calculation
N	N	LIQUID	Visible	Hidden	Hidden
N	N	MIXED	Visible	Hidden	Hidden
N	N	VAPOR	Hidden	Hidden	Hidden
N	Y	LIQUID	Hidden	Visible	Visible
N	Y	MIXED	Hidden	Visible	Visible
N	Y	VAPOR	Hidden	Hidden	Hidden
Y	Y	LIQUID	Hidden	Hidden	Hidden
Y	Y	MIXED	Hidden	Hidden	Hidden
Y	Y	VAPOR	Hidden	Hidden	Hidden
Y	N	LIQUID	Hidden	Hidden	Hidden
Y	N	VAPOR	Hidden	Hidden	Hidden
Y	N	MIXED	Hidden	Hidden	Hidden

And this is the code I have currently; it does not account for the G_Feedtype variable.

    Dim IsNo As Boolean
        If UCase(Range("G_TwoTiered").Value) = "Y" Then
        Sheets("Spouts").Visible = False
        Sheets("Spouts2").Visible = False
        Sheets("Redistribution Calculations").Visible = False
    Else
        IsNo = UCase(Range("G_CapturedAbove").Value) = "N"
        Sheets("Spouts").Visible = IsNo
        Sheets("Spouts2").Visible = Not IsNo
        Sheets("Redistribution Calculations").Visible = Not IsNo
    End If

Is there a way I can update this code, or do I need to try something else? Basically, in addition to what is dictated in the above code, anytime FeedType is set to Mixed or Liquid, I need to hide Spouts, Spouts2 and Redistribution Calculations sheets.

 

[ranman256]

past code into a module, then run using sheet to enter into cells, or a form
USAGE:

'ENTER THE PARAMS
getHideVals "Y", "Y", "LIQUID", bSpoutHide, bSpouts2Hide, bRedistHide

'HIDE COLS
Columns("D:D").EntireColumn.Hidden = bSpoutHide
Columns("E:E").EntireColumn.Hidden = bSpouts2Hide
Columns("F:F").EntireColumn.Hidden = bRedistHide

Private bSpoutHide As Boolean, bSpouts2Hide As Boolean, bRedistHide As Boolean
Const kHIDE = "HIDDEN"

Public Function getHideVals(ByVal pvG2Tier, ByVal gvGCapAbove, ByVal pvGFeed, pbSpout As Boolean, pbSpouts2 As Boolean, pbRedistrib As Boolean)
Dim iRows As Long

iRows = 13
Range("A2").Select

'turn ON filter
Selection.AutoFilter
With ActiveSheet.Range("$A$1:$F$" & iRows)
    .AutoFilter Field:=1, Criteria1:=pvG2Tier
    .AutoFilter Field:=2, Criteria1:=gvGCapAbove
    .AutoFilter Field:=3, Criteria1:=pvGFeed
End With

  'which row is winner?
While ActiveCell.Height = 0
   ActiveCell.Offset(1, 0).Select
   If ActiveCell.Value = "" Then
     MsgBox "Parameters not found"
     End
   End If
Wend

pbSpout = UCase(ActiveCell.Offset(0, 3).Value) = kHIDE
pbSpouts2 = UCase(ActiveCell.Offset(0, 4).Value) = kHIDE
pbRedistrib = UCase(ActiveCell.Offset(0, 5).Value) = kHIDE

'turn off filter
Selection.AutoFilter

End Function