multiple worksheet change events

[SarahClark01]

Hi there,

I am trying to adjust excel so that when certain criteria is met, additional rows are unhidden.

For example, when a number is entered, it unhides some rows (1 is entered 2 rows unhidden, 2 is entered four rows are unhidden etc). Once the rows are hidden, if a word is entered in a specific cell, another group of rows are unhidden.

Individually this code works, together it does not.

code is below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("e3")) Is Nothing Then
ActiveSheet.Rows("4:13").EntireRow.Hidden = True
If Target = 0 Then
ActiveSheet.Rows("7:13").EntireRow.Hidden = True
ActiveSheet.Rows("4:6").EntireRow.Hidden = True
ElseIf Target = 1 Then
ActiveSheet.Rows("4:6").EntireRow.Hidden = False
ActiveSheet.Rows("7:13").EntireRow.Hidden = False
ElseIf Target = 2 Then
End If

End If

End Sub

Private Sub Worksheet_Change1(ByVal value As Range)
If Not Intersect(value, Range("E4")) Is Nothing Then
ActiveSheet.Rows("24:30").EntireRow.Hidden = True
If value = "FS" Then
ActiveSheet.Rows("24:30").EntireRow.Hidden = False
ActiveSheet.Rows("24:30").EntireRow.Hidden = False
ElseIf value = "" Then
End If

End If
End Sub

what am I doing wrong?

Sarah

 

[Scott T]

This line

If Not Intersect(value, Range("E4")) Is Nothing Then

should be

If Not Intersect(Target, Range("E4")) Is Nothing Then

 

[SarahClark01]

Thanks Scott.

Made that change.

The first part works with the fields being unhidden, but when I enter FS in a cell the next part of the code does not work. No error is being thrown?

 

[Scott T]

This line is testing if the cell that changed is E4 if it is not then the hide rows code does not run.

If Not Intersect(Target, Range("E4")) Is Nothing Then

You want to change this value to target assuming you want to test what was just entered.

If Target = "FS" Then

Also not that VBA is case sensitive so fs<>FS
You can use UCASE to make the comparison upper case to upper case so even if lower case is entered it will still find a match.

If UCase(Target) = "FS" Then

 

[Norie]

Sarah

You can only have one Change event for a worksheet, your second set of code will never be triggered.

What you could do is combine both sets of code like this.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("e3")) Is Nothing Then
    
        Rows("4:13").EntireRow.Hidden = True
        
        With Target
            If .value = 0 Then
                Rows("7:13").EntireRow.Hidden = True
                Rows("4:6").EntireRow.Hidden = True
            ElseIf .value = 1 Then
                Rows("4:6").EntireRow.Hidden = False
                Rows("7:13").EntireRow.Hidden = False
            ElseIf .value = 2 Then
                ' do something else
            End If
        End With

    End If

    If Not Intersect(Target, Range("E4")) Is Nothing Then
    
        Rows("24:30").EntireRow.Hidden = True
        If Target.value = "FS" Then
            Rows("24:30").EntireRow.Hidden = False
            Rows("24:30").EntireRow.Hidden = False
        ElseIf Target.value = "" Then
            ' do something else
        End If

    End If
    
End Sub