Brenda:
Is the code you provided a subset of a VBA Sub ? I do not see your code wrapped within
Public Sub TwoTier()
End Sub
Please provide the rest of your code or a short version of the applicable code.
There is a lot of code; I can supply the whole thing. I am not very experienced with this, so I am just kind of flying by the seat of my pants. I can usually make things work, but it's probably not the best way to do it.
By the way ... there is an error in the code you provided...
I think Vapor should be in quotes ("Vapor"). Otherwise you have an undefined variable.
Do you have the Option Explicit statement in your code. This would have caught that error.
I do not; I will check into that.
VBA Code:
'...
'If Redistribution is set to no; head heights are pulled from the spouts tab
'ElseIf FeedType = Vapor Then
ElseIf FeedType = "Vapor" Then '<< when corrected should look like this
Sheets("Input").Range("L6") = "NA"
Sheets("Input").Range("L7") = "NA"
Sheets("Input").Range("L8") = "NA"
End If
'....
Your code set L6:L8 to "See Tab" with bugs fixed.
I have fixed this. Thanks.
It would help to restructure your code a bit. You are making unnecessary tests in your ElseIf's
What happens with your code if TwoTier is not "Y" or "N", or ReDist is not "Y" or "N", and what values other than "Vapor" or not "Vapor" can FeedType be set to?
You might do some error checking to make your variables are properly set. E.g. If TwoTier is not "Y" or "N" - error message, the same for ReDist, Not sure about FeedType.
Wait till you see the rest of it
I'm not sure how to restructure it. I know it's long and convoluted, but I'm really not sure how else to do it.
There have to be values in those fields (TwoTier, ReDist, FeedType). And the spreadsheet starts with those values pre-filled; they can be changed. The FeedType is filled in based on input information supplied and cannot be blank (it's a protected cell). The other two, TwoTier and ReDistribution can be left blank. It appears when that happens, the spreadsheet doesn't error, it just doesn't change anymore.
Below is the entire code; everything is working (thanks to some help from in here earlier) except for the top sub, the one shared prior. If you have advice on how to clean this, make it run better, I would greatly accept and appreciate that!
VBA Code:
Private bRunning As Boolean
Private Sub Worksheet_Activate()
Worksheets("Input").Protect
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If bRunning Then Exit Sub
Dim FeedType As Range
'Dim SlotOrHole As Range
Dim Redist As Range
Dim RDesignHead As Range
Dim DesignHead As Range
Dim RTDHead As Range
Dim TDHead As Range
Dim RTUHead As Range
Dim TUHead As Range
Dim TwoTier As Range
Set FeedType = Sheets("Input").Range("H12")
'Set SlotOrHole = Sheets("Input").Range("H19")
Set Redist = Sheets("Input").Range("H18")
Set RDesignHead = Sheets("Spouts2").Range("P3")
Set RTDHead = Sheets("Spouts2").Range("R3")
Set RTUHead = Sheets("Spouts2").Range("Q3")
Set DesignHead = Sheets("Spouts").Range("P3")
Set TDHead = Sheets("Spouts").Range("R3")
Set TUHead = Sheets("Spouts").Range("Q3")
Set TwoTier = Sheets("Input").Range("J16")
If TwoTier = "Y" Then
Sheets("Input").Range("L6") = "See Tab"
Sheets("Input").Range("L7") = "See Tab"
Sheets("Input").Range("L8") = "See Tab"
'If Two Tier is set to yes; all head heights should report the value "See Tab"
ElseIf FeedType <> "Vapor" And TwoTier = "N" And Redist = "Y" Then
Sheets("Input").Range("L6") = RDesignHead
Sheets("Input").Range("L7") = RTDHead
Sheets("Input").Range("L8") = RTUHead
'If Redistrubtion is set to yes; head heights are pulled from the spouts2 tab
ElseIf FeedType <> "Vapor" And TwoTier = "N" And Redist = "N" Then
Sheets("Input").Range("L6") = DesignHead
Sheets("Input").Range("L7") = TDHead
Sheets("Input").Range("L8") = TUHead
'If Redistribution is set to no; head heights are pulled from the spouts tab
ElseIf FeedType = "Vapor" Then
Sheets("Input").Range("L6") = "NA"
Sheets("Input").Range("L7") = "NA"
Sheets("Input").Range("L8") = "NA"
End If
Dim IsNo As Boolean
IsNo = UCase(Range("G_CapturedAbove").Value) = "N"
Sheets("Spouts").Visible = IsNo
Sheets("Spouts2").Visible = Not IsNo
Sheets("Redistribution Calculations").Visible = Not IsNo
'Shows Spouts if no redistribution, and spouts2 if there is redistribution
If Range("G_TwoTiered").Value = ("N") Then
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
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = True
'Hides all two-tiered distributor sheets if Two-Tiered is set to No
ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("5") Then
Sheets("5 Downcomer~4 Trough").Visible = True
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
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = False
ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("6") Then
Sheets("5 Downcomer~4 Trough").Visible = False
Sheets("6 Downcomer~4 Trough").Visible = True
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
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = False
ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("8") And Range("C_TroughsRequired").Value = (6) Then
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 = True
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
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = False
ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("8") And Range("C_TroughsRequired").Value = (4) Then
Sheets("5 Downcomer~4 Trough").Visible = False
Sheets("6 Downcomer~4 Trough").Visible = False
Sheets("8 Downcomer~4 Trough").Visible = True
Sheets("8 Downcomer~6 Trough").Visible = Fals
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
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = False
ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("10") And Range("C_TroughsRequired").Value = (6) Then
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 = True
Sheets("12 Downcomer~4 Trough").Visible = False
Sheets("12 Downcomer~6 Trough").Visible = False
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = False
ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("10") And Range("C_TroughsRequired").Value = (4) Then
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 = True
Sheets("10 Downcomer~6 Trough").Visible = False
Sheets("12 Downcomer~4 Trough").Visible = False
Sheets("12 Downcomer~6 Trough").Visible = False
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = False
ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("12") And Range("C_TroughsRequired").Value = (6) Then
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 = True
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = False
ElseIf Range("G_TwoTiered").Value = ("Y") And Range("G_NoofDowncomers").Value = ("12") And Range("C_TroughsRequired").Value = (4) Then
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 = True
Sheets("12 Downcomer~6 Trough").Visible = False
Sheets("Weighted Density").Visible = False
'Sheets("Spouts").Visible = False
'All ElseIf statements control which sheets are hidden and which are shown based on how many downcomers
'are selected.
'If there is no sheet available for the number of downcomers shown, nothing will show
End If
End Sub