The spreadsheet is a checklist.
The users answer to each question by "Yes" or "No" (via a drop down)
Before that, another code establishes which user profile is applicable ("A", "B" or "C") and hides other rows (that are by default not applicable to the concerned profile)
This code below should hide or show rows depending on the answer to specific questions.
and if the user profile is "B" (Sheet5.Cells(8, 10).Value = "B") , the three last IF statements statements should not be checked (because the profile code directly hides the corresponding rows)
If profile is not "B" then all statements should be checked.
I tried this but it doesn't work:
if 'profile is "B" then
stop
end if
thank you for any help.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("D24")) Is Nothing Then 'ch. systems
Sheets("CL_assurance_package").Rows("26:30").EntireRow.Hidden = Not (Target.Value) = "Yes"
Sheets("CL_assurance_package").Rows("26:30").EntireRow.Hidden = (Target.Value) = "No"
End If
If Not Intersect(Target, Range("D33")) Is Nothing Then 'ch.strategy
Sheets("CL_assurance_package").Rows("35:36").EntireRow.Hidden = Not (Target.Value) = "Yes"
Sheets("CL_assurance_package").Rows("35:36").EntireRow.Hidden = (Target.Value) = "No"
End If
If Not Intersect(Target, Range("D39")) Is Nothing Then 'ct. systems
Sheets("CL_assurance_package").Rows("41:55").EntireRow.Hidden = Not (Target.Value) = "Yes"
Sheets("CL_assurance_package").Rows("41:55").EntireRow.Hidden = (Target.Value) = "No"
End If
' *** at this point if Sheet5.Cells(8, 10).Value = "B" I want the code to stop checking the statements below ***
' *** If value in Sheet5.Cells(8, 10) is different than "B" then continue checking all the statements***
If Not Intersect(Target, Range("D58")) Is Nothing Then 'ct. operations
Sheets("CL_assurance_package").Rows("60:106").EntireRow.Hidden = Not (Target.Value) = "Yes"
Sheets("CL_assurance_package").Rows("60:106").EntireRow.Hidden = (Target.Value) = "No"
End If
If Not Intersect(Target, Range("D109")) Is Nothing Then 'ct. acounts
Sheets("CL_assurance_package").Rows("111:123").EntireRow.Hidden = Not (Target.Value) = "Yes"
Sheets("CL_assurance_package").Rows("111:123").EntireRow.Hidden = (Target.Value) = "No"
End If
If Not Intersect(Target, Range("D109")) Is Nothing Then 'cl. acounts
Sheets("CL_assurance_package").Rows("126:160").EntireRow.Hidden = Not (Target.Value) = "Yes"
Sheets("CL_assurance_package").Rows("126:160").EntireRow.Hidden = (Target.Value) = "No"
End If
Application.EnableEvents = True
End Sub
Bookmarks