I am having an issue hiding several rows based on a cell selection. The cell selection has a named range of "VS_Core_ProjectType". Depending on the selection, Core or Vendor Switch, certain rows will hide and un-hide. In this case I am having issues getting rows 67 to hide when Vendor Switch is Selected and rows 70:71 to hide when Core is selected. The code works fine otherwise. Not sure why those ones will not work as expected.
Things I have already tried. I sorted my code sequential order by the rows, I have combined rows under the named range of the Select Case, I have save, closed reopened and also have completely retyped the code several times. Below you will notice that I have duplicate code for the rows in question. I commented the ones out under the Else If statement hoping if I put all together under the one Select Case, that if might make a difference. Maybe a 2nd, 3rd or 4th pair of eyes might see something that I do not.
Thank You In Advance
Danny
Private Sub Worksheet_Change(ByVal Target As Range)
'Unprotect Sheet
'With Sheets("Questions")
'.Unprotect 'Password:="mpi"
ActiveSheet.Activate
If Not Application.Intersect(Range("VS_Core_ProjectType"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Select One": Rows("8:12").EntireRow.Hidden = False
Case Is = "Core": Rows("8:12").EntireRow.Hidden = True
Case Is = "Vendor Switch": Rows("8:12").EntireRow.Hidden = False
Case Is = "Select One": Rows("67").EntireRow.Hidden = False
Case Is = "Core": Rows("67").EntireRow.Hidden = False
Case Is = "Vendor Switch": Rows("67").EntireRow.Hidden = True
Case Is = "Select One": Rows("70:71").EntireRow.Hidden = False
Case Is = "Core": Rows("70:71").EntireRow.Hidden = True
Case Is = "Vendor Switch": Rows("70:71").EntireRow.Hidden = False
End Select
'Hide Questions if Vendor Switch
'ElseIf Not Application.Intersect(Range("VS_Core_ProjectType"), Range(Target.Address)) Is Nothing Then
'Select Case Target.Value
'Case Is = "Select One": Rows("67").EntireRow.Hidden = False
'Case Is = "Core": Rows("67").EntireRow.Hidden = False
'Case Is = "Vendor Switch": Rows("67").EntireRow.Hidden = True
'End Select
'Hide Questions if Core
'ElseIf Not Application.Intersect(Range("VS_Core_ProjectType"), Range(Target.Address)) Is Nothing Then
'Select Case Target.Value
'Case Is = "Select One": Rows("70:71").EntireRow.Hidden = False
'Case Is = "Core": Rows("70:71").EntireRow.Hidden = True
'Case Is = "Vendor Switch": Rows("70:71").EntireRow.Hidden = False
'End Select
'Hide Change Platform Yes or No (line 11)
ElseIf Not Application.Intersect(Range("VS_Core_ChangePlatform_YN"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("12").EntireRow.Hidden = True
Case Is = "Yes": Rows("12").EntireRow.Hidden = False
Case Is = "Select One": Rows("12").EntireRow.Hidden = False
End Select
' Hide Customer Select Pins (Questions 1.05)
ElseIf Not Application.Intersect(Range("VS_Core_BinQuest1.06_YN"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("27:28").EntireRow.Hidden = True
Case Is = "Yes": Rows("27:28").EntireRow.Hidden = False
Case Is = "Select One": Rows("27:28").EntireRow.Hidden = False
End Select
'Show Mixed Deposit Question 2.01a (Line38)when the following switches are slected Fiserv Card Service,Vantiv, VisaDPS, Coop, Efunds, FIS Intecept
ElseIf Not Application.Intersect(Range("VS_Core_SwitchVendor2"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Select A Switch": Rows("40").EntireRow.Hidden = True
Case Is = " Elan": Rows("40").EntireRow.Hidden = True
Case Is = " Fiserv Card Services": Rows("40").EntireRow.Hidden = False
Case Is = " Coop": Rows("40").EntireRow.Hidden = False
Case Is = " FIS (Efunds)": Rows("40").EntireRow.Hidden = False
Case Is = " FIS (Intercept)": Rows("40").EntireRow.Hidden = False
Case Is = " Visa DPS": Rows("40").EntireRow.Hidden = False
Case Is = " World Pay (Vantiv)": Rows("40").EntireRow.Hidden = False
End Select
' Will you allow ATM Deposits? (If Yes, complete questions 2.01a - 2.01f, If No, Skip to Question 2.02)
ElseIf Not Application.Intersect(Range("VS_Core_GenQuest2.01_YN"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("39:47").EntireRow.Hidden = True
Case Is = "Yes": Rows("39:47").EntireRow.Hidden = False
Case Is = "Select One": Rows("39:47").EntireRow.Hidden = False
End Select
'Hide Do you have any branches that will remain open after the switch cut? (If Yes, Complete Question 2.07a)
ElseIf Not Application.Intersect(Range("VS_Core_GenQuest2.07_YN"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("62").EntireRow.Hidden = True
Case Is = "Yes": Rows("62").EntireRow.Hidden = False
Case Is = "Select One": Rows("62").EntireRow.Hidden = False
End Select
' Hide Instant Issue if Currently does have it or not implementing it during project conversion.
ElseIf Not Application.Intersect(Range("VS_Core_InstantIssue_YN"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("88:89").EntireRow.Hidden = True
Case Is = "Yes": Rows("88:89").EntireRow.Hidden = False
Case Is = "Select One": Rows("88:89").EntireRow.Hidden = False
Case Is = "No": Rows("94:115").EntireRow.Hidden = True
Case Is = "Yes": Rows("94:115").EntireRow.Hidden = False
Case Is = "Select One": Rows("94:115").EntireRow.Hidden = False
End Select
ElseIf Not Application.Intersect(Range("VS_Core_NewInstantIssue_YN"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Select One": Rows("94:115").EntireRow.Hidden = False
Case Is = "No": Rows("91:115").EntireRow.Hidden = True
Case Is = "Yes": Rows("91:115").EntireRow.Hidden = False
End Select
End If
'Protect Sheet
'.Protect 'Password:="mpi"
'End With
Display More