How to use IF statements

Question

Hi guys I’m trying to make a fill-in form where certain rows get hidden when certain options are selected the first part of the script works perfectly but adding a second optional cell (B31) seems to be problematic can’t get it to work can someone please help? this is the script I found on the internet and adapted to my purpose

Private Sub worksheet_change(ByVal Target As Range)
    If Target.Address = ("$B$6") Then
        If Target.Text = "Pollution" Then
            Rows("21:28").EntireRow.Hidden = False
            Rows("29:97").EntireRow.Hidden = True
        ElseIf Target.Text = "Select option" Then
            Rows("21:97").EntireRow.Hidden = True
        ElseIf Target.Text = "Fire" Then
            Rows("21:29").EntireRow.Hidden = True
            Rows("30:42").EntireRow.Hidden = False
            Rows("43:97").EntireRow.Hidden = True
        ElseIf Target.Text = "Collision, Grounding & Stranding" Then
            Rows("21:43").EntireRow.Hidden = True
            Rows("44:57").EntireRow.Hidden = False
            Rows("58:97").EntireRow.Hidden = True
        ElseIf Target.Text = "Technical Failure" Then
            Rows("21:58").EntireRow.Hidden = True
            Rows("59:67").EntireRow.Hidden = False
            Rows("68:97").EntireRow.Hidden = True
        ElseIf Target.Text = "Crew member missing / Man over board" Then
            Rows("21:68").EntireRow.Hidden = True
            Rows("69:79").EntireRow.Hidden = False
            Rows("80:97").EntireRow.Hidden = True
        ElseIf Target.Text = "Injury / Fatality" Then
            Rows("21:80").EntireRow.Hidden = True
            Rows("81:87").EntireRow.Hidden = False
            Rows("88:97").EntireRow.Hidden = True
        ElseIf Target.Text = "Cargo shift / damage" Then
            Rows("21:88").EntireRow.Hidden = True
            Rows("89:97").EntireRow.Hidden = False
        End If
    If Target.Address = ("$B$31") Then
        If Target.Text = "Engine room" Then
            Rows("40:42").EntireRow.Hidden = True
            Rows("30:39").EntireRow.Hidden = False
            End If
        End If
    End If
End Sub

Thanks in advance

Answer 1

You have a predetermined patten for selecting which rows will be shown and which will not. Consequently it is possible to refactor your code so that no 'If/ElseIf/End' are required for the actions of showing/hiding rows.

There is nothing wrong with using 'if', 'if then else' etc so I'm providing the code below as an exercide in how to think differently.

I'm not a user of Excel so my apologies in advance if I've made any mistakes in the Excel object syntax.

The code compiles without errors and generates no unexpected code inspection results with RubberDuck.

Option Explicit

Private Type State

    JumpRanges              As Scripting.Dictionary
    B6JumpTable             As Scripting.Dictionary
    B31JumpTable            As Scripting.Dictionary
    
End Type

Private s   As State

Private Sub worksheet_change(ByVal Target As Range)

    If s.JumpRanges Is Nothing Then InitialiseJumpTables

    ActiveSheet.Rows.Item("21:97").EntireRow.Hidden = True
    
    If s.JumpRanges.exists(Target.Address) Then

          ActiveSheet.Rows(s.JumpRanges.Item(Target.Address).Item(Target.Text)).EntireRow.Hidden = False
        
    Else
    
        Err.Raise _
            17, _
            "Range Error", _
            "The range " & Target.Address & "does not exist in the JumpRanges dictionary"
            
    End If
            
    
End Sub

Public Sub InitialiseJumpTables()

    Set s.B6JumpTable = New Scripting.Dictionary
    
    With s.B6JumpTable
    
        .Add "Pollution", "21:28"
        .Add "Select option", "21:97"
        .Add "Fire", "30:42"
        .Add "Collision, Grounding & Stranding", "44:57"
        .Add "Technical Failure", "59:67"
        .Add "Crew member missing / Man over board", "69:79"
        .Add "Injury / Fatality", "81:87"
        .Add "Cargo shift / damage", "81:87"
    
    End With
    
    Set s.B31JumpTable = New Scripting.Dictionary
    
    With s.B31JumpTable
    
        .Add "Engine room", "30:39"
        
    End With
    
    Set s.JumpRanges = New Scripting.Dictionary
    
    With s.JumpRanges
    
        .Add "$B$6", s.B6JumpTable
        .Add "$B$31", s.B31JumpTable
        
    End With
    
End Sub

Answer 2

Your If blocks are nested wrongly. Try something like this - note it's easier to only unhide the rows you want after first hiding everything.

Private Sub worksheet_change(ByVal Target As Range)
    
    If Target.Address = ("$B$6") Then
        Me.Rows("21:97").EntireRow.Hidden = True 'hide everything
        '...then unhide only the required rows
        Select Case Target.Text
            Case "Pollution":
                Me.Rows("21:28").EntireRow.Hidden = False
            Case "Fire":
                Me.Rows("30:42").EntireRow.Hidden = False
            Case "Collision, Grounding & Stranding":
                Me.Rows("44:57").EntireRow.Hidden = False
            Case "Technical Failure":
                Me.Rows("59:67").EntireRow.Hidden = False
            Case "Crew member missing / Man over board":
                Me.Rows("69:79").EntireRow.Hidden = False
            Case "Injury / Fatality":
                Me.Rows("81:87").EntireRow.Hidden = False
            Case "Cargo shift / damage":
                Me.Rows("89:97").EntireRow.Hidden = False
        End Select
    End If
    
    If Target.Address = ("$B$31") Then
        If Target.Text = "Engine room" Then
            Me.Rows("40:42").EntireRow.Hidden = True
            Me.Rows("30:39").EntireRow.Hidden = False
        End If
    End If
    
End Sub