Multiple Worksheet Change events??? "macro" needed to hide/unhide rows multiple instances

Is it possible to program multiple Worksheet Change Events into the same workbook? I have a lengthy set of tasks, each with 2-4 Options. I need to use list boxes (preferably) to select Options used to complete Tasks. Selecting a unique Option for each Task will hide and unhide corresponding bundles of rows in the next sheet titled "Tasks". Please take a look at the attached example. Aks2014 kindly provided me the Worksheet Change code that works for the list box in cell J18. However, I need to do this same function for about 50 tasks, some with differing number of Options, but many with the same number of Options (although each Option and its bundles of rows would be unique to the individual Tasks they reference.) How would I program it so that Sheet1 cell J20 would likewise hide or unhide its corresponding rows in the "Tasks" sheet?

Note: to expedite your effort I've added the equivalent (partial) coding for this J20 list box in cell A133 in the "Tasks" sheet. Also note that I am ok to use individual macros for each Task and/or Option if that is easier than Worksheet Change Events.

Thanks for your help. I am so close to getting this figured out but I need more technical expertise....

(signed)
The Needy Neophyte

Hi dgibney,

My personal preference is to keep the Worksheet code as short as possible, and do most of the work in an ordinary code module. This approach has slightly more overhead, but I think it has the advantages of:
a. Keeping the Worksheet module code cleaner and easier to understand.
b. It is often easier to update ordinary code modules in workbooks that others are using.
c. It is easier usually much easier to debug 'Ordinary Code Modules', because even if the routines are not standalone (no formal parameters), they can be tested using small test code modules.

For example in Sheet1 code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("J18")) Is Nothing Then
      Call Sheet1ChangeEventHandler(Target)
    ElseIf Not Intersect(Target, Range("J20")) Is Nothing Then
      Call Sheet1ChangeEventHandler(Target)
    End If

End Sub

In an ordinary module:

Sub TestSheet1ChangeEventHandlerForJ18()
  Sheet1ChangeEventHandler (Sheets("Sheet1").Range("J18"))
End Sub

Sub TestSheet1ChangeEventHandlerForJ20()
  Sheet1ChangeEventHandler (Sheets("Sheet1").Range("J20"))
End Sub

Sub Sheet1ChangeEventHandler(ByVal Target As Range)

  Dim sAddress As String
  Dim sValue As String
  
  'Get the address of the cell that changed without '$' signs
  sAddress = Target.Address(False, False)
  
  'Get the value in the cell without leading and trailing spaces
  'Allow processing to continue if the cell does not contain text
  On Error Resume Next
  sValue = Trim(Target.Value)
  On Error GoTo 0

  'Perform 'J18' only processing
  If sAddress = "J18" Then
    '...
  End If
  
  'Perform 'J20' only processing
  If sAddress = "J20" Then
    '...
  End If
  
  
  'Perform common processing
  '...

End Sub

Lewis

Thanks for the response LJ. Unfortunately I only understood about a third of it, sorry! I tried to create macros with the title "TestSheet1ChangeEventHandlerForJ20()" and "Sheet1ChangeEventHandlerForJ20()" but Excel would not accept them. It would probably be most helpful, and educational, if you could write out the code for on the list boxes and let me know if there is anything special about how are copied into the Worksheet or ordinary module. (I'm still confused a bit by what these acronyms all mean....)

Thanks again for your response. The (truly) Needy Neophyte...

See the attached code for compartmentalizing the code for each cell in Sheet1. I think it is easier to maintain and less prone to mistakes.

I am not sure how to handle the common instructions, because it is hard for me to picture what the sheet will look like when you have 50 choices instead of only 2. I would leave it alone for now or perhaps just put the 'common instructions' in one place at the top of the worksheet all the time.

Lewis

Sheet1 code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("J18")) Is Nothing Then
      Call ProcessSheet1ChangeOnCellJ18(Target)
    ElseIf Not Intersect(Target, Range("J20")) Is Nothing Then
      Call ProcessSheet1ChangeOnCellJ20(Target)
    End If

End Sub

Module ModDisplayOrHideRows code:

Option Explicit


Sub ProcessSheet1ChangeOnCellJ18(ByVal Target As Range)

  Dim sAddress As String
  Dim sValue As String
  
  'Get the address of the cell that changed without '$' signs
  sAddress = Target.Address(False, False)
  
  'Get the value in the cell without leading and trailing spaces
  'Allow processing to continue if the cell does not contain text
  On Error Resume Next
  sValue = Trim(Target.Value)
  On Error GoTo 0

  'Perform 'J18' only processing
  If sAddress = "J18" Then
    ' HIDE ROWS IN TASKS WORKSHEET BASED ON USER SELECTION
    If Target.Value = "Not Pursuing" Then
        ActiveWorkbook.Sheets("Tasks").Rows("104:104").EntireRow.Hidden = False
        ActiveWorkbook.Sheets("Tasks").Rows("105:117").EntireRow.Hidden = True
    ElseIf Target.Value = "Option 1" Then
        ActiveWorkbook.Sheets("Tasks").Rows("104:104").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("105:113").EntireRow.Hidden = False
        ActiveWorkbook.Sheets("Tasks").Rows("114:116").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("117:117").EntireRow.Hidden = False
    ElseIf Target.Value = "Option 2" Then
        ActiveWorkbook.Sheets("Tasks").Rows("104:104").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("105:109").EntireRow.Hidden = False
        ActiveWorkbook.Sheets("Tasks").Rows("110:113").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("114:117").EntireRow.Hidden = False
    Else
        'This branch of code should NEVER be selected.
        MsgBox "SOFTWARE INTEGRITY ERROR.  Illegal selection on:" & vbCrLf & _
               "Sheet 'Tasks'" & vbCrLf & _
               "Cell: '" & sAddress & "'" & vbCrLf & _
               "Value: '" & sValue & "'"
        Stop
    End If
  End If
    
End Sub


Sub ProcessSheet1ChangeOnCellJ20(ByVal Target As Range)

  Dim sAddress As String
  Dim sValue As String
  
  'Get the address of the cell that changed without '$' signs
  sAddress = Target.Address(False, False)
  
  'Get the value in the cell without leading and trailing spaces
  'Allow processing to continue if the cell does not contain text
  On Error Resume Next
  sValue = Trim(Target.Value)
  On Error GoTo 0

  
  'Perform 'J20' only processing
  If sAddress = "J20" Then
    If Target.Value = "Not Pursuing" Then
        ActiveWorkbook.Sheets("Tasks").Rows("120:120").EntireRow.Hidden = False
        ActiveWorkbook.Sheets("Tasks").Rows("121:131").EntireRow.Hidden = True
    ElseIf Target.Value = "Option A" Then
        ActiveWorkbook.Sheets("Tasks").Rows("120:120").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("121:127").EntireRow.Hidden = False
        ActiveWorkbook.Sheets("Tasks").Rows("128:130").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("131:131").EntireRow.Hidden = False
    ElseIf Target.Value = "Option B" Then
        ActiveWorkbook.Sheets("Tasks").Rows("120:120").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("121:124").EntireRow.Hidden = False
        ActiveWorkbook.Sheets("Tasks").Rows("125:127").EntireRow.Hidden = True
        ActiveWorkbook.Sheets("Tasks").Rows("128:131").EntireRow.Hidden = False
    Else
        'This branch of code should NEVER be selected.
        MsgBox "SOFTWARE INTEGRITY ERROR.  Illegal selection on:" & vbCrLf & _
               "Sheet 'Tasks'" & vbCrLf & _
               "Cell: '" & sAddress & "'" & vbCrLf & _
               "Value: '" & sValue & "'"
        Stop
    End If
  End If
  
End Sub

Lewis, YOU are the man! This works perfectly. And, thanks for sending the educational instructions, they help me to better understand code relationships.

THANK YOU! THANK YOU! THANK YOU!

David