Excel multiple worksheet change events

[EJGAJG]

I try to hide and unhide several rows on the conditions below.
I need this for 6 more same conditions on other row.
For now only the first condition is working.

Private Sub Worksheet_Change(ByVal Target As Range)
' Check1:
Dim KeyCells As Range



' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("A3")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
= "yes" Then

' Display a message when one of the designated cells has been
' changed.
' Place your code here.

Rows("4:6").EntireRow.Hidden = True

ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _
= "no" Then
Rows("4:6").EntireRow.Hidden = False
End If

Exit Sub

' Check2:
Dim Netkwaliteit As Range

' The variable Netkwaliteit contains the cells that will
' cause an alert when they are changed.
Set Netkwaliteit = Range("A8")

If Not Application.Intersect(Netkwaliteit, Range(Target.Address)) _
= "yes" Then

' Display a message when one of the designated cells has been
' changed.
' Place your code here.

Rows("20:30").EntireRow.Hidden = True

ElseIf Not Application.Intersect(Netkwaliteit, Range(Target.Address)) _
= "no" Then
Rows("20:30").EntireRow.Hidden = False
End If


End Sub

 

[Zot]

Try if this works. If you key in something in A8 and hide rows 20~30, it will get unhide if you have any change in any other cell. That's how I see how your code is

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Replace(Target.Address, "$", "")
    Case "A3"
        Rows("4:6").EntireRow.Hidden = True
    Case "A8"
        Rows("20:30").EntireRow.Hidden = True
    Case Else
        Rows.EntireRow.Hidden = False
End Select

End Sub

 

[rollis13]

Hi everyone.
I'm late but if I correctly understood the request, have try with my version:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rRows  As Range
    Select Case Target.Address(0, 0)
        Case "A3"
            Set rRows = Range("4:6")
        Case "A8"
            Set rRows = Range("20:30")
        Case Else
            Exit Sub
    End Select
    If Target = "yes" Then rRows.EntireRow.Hidden = True
    If Target = "no" Then rRows.EntireRow.Hidden = False
End Sub

 

[dmt32]

Hi,
welcome to forum

another way maybe you can try

Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells        As Range
    Dim KeyAddress      As String
    Dim HideRows        As Variant, m As Variant
   
    ' The variable KeyAddress contains the cells addresses that will
    ' cause an alert when they are changed by user entry.
    KeyAddress = "A3,A8"
   
    'Variable HideRows contains in an array of all rows to be hidden
    ' when a keycell is changed.
    HideRows = Array("4:6", "20:30")
   
    Set KeyCells = Range(KeyAddress)
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        m = Application.Match(Target.Address(0, 0), Split(KeyAddress, ","), 0)
        Rows(HideRows(m)).EntireRow.Hidden = Not LCase(Target.Value) = "yes"
    End If
   
End Sub

Add to the values shown in BOLD for addresses & ranges you want code to apply to

Note: The Option Base 1 statement - this MUST sit at the very TOP of your sheets code page OUTSIDE any procedure

Hope Helpful

Dave

 

[Zot]

Hi everyone.
I'm late but if I correctly understood the request, have try with my version:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rRows  As Range
    Select Case Target.Address(0, 0)
        Case "A3"
            Set rRows = Range("4:6")
        Case "A8"
            Set rRows = Range("20:30")
        Case Else
            Exit Sub
    End Select
    If Target = "yes" Then rRows.EntireRow.Hidden = True
    If Target = "no" Then rRows.EntireRow.Hidden = False
End Sub

Looks like you understood this right.