Nov 30 2021 06:28 AM
Hi I'm really really beginner at this macro/VBA stuff. My expertise goes as far as googling and using formulas on forums etc
I'm trying to produce a dynamic 'drop down flow chart selector' ... thing which will hide and unhide rows to remove or add the next question/drop down selection.
It consists of 4 drop downs in total and has drop down selections which will either stop the person using it from proceeding or advance to the next question to continue the process.
Drops downs are in Cells C13, C25, C37, C49
I wanted to start with only rows 1:21 visible
DD1 - 4 codes can be selected
Code 1 - If selected hide row 22:58
Code 2 - If selected move to DD2 (C25) unhide rows 22:32
Code 3 - If selected move to DD2 (C25) unhide rows 22:32
Code 4 - If selected hide rows 22:58
DD2 - has 'Yes' and 'No' answer
Code 2 - Yes - Continue to DD3 (C37) unhide rows 33:44
Code 2 - No - hide rows 33:58
Code 3 - Yes - Continue to DD3 (C37) rows 33:44
Code 3 - No - hide rows 33:58
DD3 - has 'Yes' and 'No' answers
Code 2 - Yes - Continue to DD4 (C49) unhide rows 45:58
Code 2 - No - hide rows 45:58
Code 3 - Yes - Continue to DD4 (C49) - unhide rows 45:58
Code 3 - No - hide rows 45:58
The following code worked well for the first part (have underlined) but I'm stuck now how to unhide/hide more rows for drop down 2 and so on.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TrigerCell As Range
Set Triggercell = Range("C13")
If Not Application.Intersect(Triggercell, Target) Is Nothing Then
If Triggercell.Value = "Code 1" Then
Rows("22:58").Hidden = True
ElseIf Triggercell.Value = "Code 4" Then
Rows("22:58").Hidden = True
ElseIf Triggercell.Value = "Code 2" Then
Rows("22:32").Hidden = False
ElseIf Triggercell.Value = "Code 3" Then
Rows("22:32").Hidden = False
End If
End If
End Sub
Any help would be appreciated, thank you
Nov 30 2021 07:06 AM
How about
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
Select Case Target.Address
Case "$C$13"
Select Case Range("C13").Value
Case "Code 2", "Code 3"
Range("A22:A32").EntireRow.Hidden = False
Range("C25").Select
Range("C25").ClearContents
Case "Code 1", "Code 4"
Range("A22:A58").EntireRow.Hidden = True
End Select
Case "$C$25"
Select Case Range("C25").Value
Case "Yes"
Range("A33:A44").EntireRow.Hidden = False
Range("C37").Select
Range("C37").ClearContents
Case "No"
Range("A33:A58").EntireRow.Hidden = True
End Select
Case "$C$37"
Select Case Range("C37").Value
Case "Yes"
Range("A45:A58").EntireRow.Hidden = False
Range("C49").Select
Range("C49").ClearContents
Case "No"
Range("A45:A58").EntireRow.Hidden = True
End Select
End Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub