Multiple Select Case Statements in a Single Procedure-CodePudding

I am attempting to Autofilter 2 tables using Select Case statements and Autofilters in a single procedure. These are running from the worksheet itself and not a separate module - I'm wondering if this is my issue, as I am not sure when it is best to use modules vs. worksheet.

I have two separate Select Case statements affecting 2 different dropdown cells - RegionChoice and ProjectType. The autofilters based on RegionChoice (the 1st of these select case statements) run perfectly. However, the second Select Case statements' filters do not appear to work at all.

Filtering based on RegionChoice should affect both tables, but filtering based on ProjectType should affect just one of the two. Please see code below and thanks!

'Autofilter table on Summary Tab & CapEx Project Table on Visual based on Region drop down on Visual Tab
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsSumm As Worksheet, wsVis As Worksheet
    Dim range_to_filter As Range, range_to_filter_2 As Range
    
    Set wsSumm = ThisWorkbook.Worksheets("Summary")
    Set wsVis = ThisWorkbook.Worksheets("Visual")
    Set range_to_filter = wsSumm.Range("A3:Z113")
    Set range_to_filter_2 = wsVis.Range("B80:T109")
    
    If Application.Intersect(Me.Range("RegionChoice"), Target) Is Nothing Then Exit Sub

    Select Case Me.Range("RegionChoice").Value
    
    'Central
        Case Me.Range("A1").Value
            wsSumm.Unprotect ("fac1")
            range_to_filter.AutoFilter Field:=4, Criteria1:="C"
            range_to_filter_2.AutoFilter Field:=5, Criteria1:="C"
    'South
        Case Me.Range("A2").Value
            wsSumm.Unprotect ("fac1")
            range_to_filter.AutoFilter Field:=4, Criteria1:="S"
            range_to_filter_2.AutoFilter Field:=5, Criteria1:="S"
    'West
        Case Me.Range("A3").Value
            wsSumm.Unprotect ("fac1")
            range_to_filter.AutoFilter Field:=4, Criteria1:="W"
            range_to_filter_2.AutoFilter Field:=5, Criteria1:="W"
    'Northeast
        Case Me.Range("A4").Value
            wsSumm.Unprotect ("fac1")
            range_to_filter.AutoFilter Field:=4, Criteria1:="NE"
            range_to_filter_2.AutoFilter Field:=5, Criteria1:="NE"
    'Clear
        Case Me.Range("A5").Value
            wsSumm.Unprotect ("fac1")
           range_to_filter.AutoFilter Field:=4
           range_to_filter_2.AutoFilter Field:=5

    End Select
    
    If Application.Intersect(Me.Range("ProjectType"), Target) Is Nothing Then Exit Sub

    Select Case Me.Range("ProjectType").Value
    
    'Refresh
        Case Me.Range("A9").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Refresh"
    'Buildout
        Case Me.Range("A10").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Buildout"
    'Maintenance
        Case Me.Range("A11").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Maintenance"
    'Expansion
      Case Me.Range("A12").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Expansion"
    'Furniture
      Case Me.Range("A13").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2, Criteria1:="Furniture"
    'Clear
      Case Me.Range("A14").Value
            wsVis.Unprotect ("fac1")
            range_to_filter_2.AutoFilter Field:=2

    End Select
End Sub

I initially had this structured as multiple if/else statements, but replaced w/ Select Case based on a SO user's feedback. It has proved a much more efficient solution, so I'd like to keep it if possible.

I was expecting the second Select Case statement based on cell ProjectType to behave in much the same way as the first based on RegionChoice. However, the autofilter statements are not running in any case.

CodePudding user response：

Here's a slightly different structuring which will reduce your duplicated code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsSumm As Worksheet, wsVis As Worksheet
    Dim range_to_filter As Range, range_to_filter_2 As Range, crit As String
    
    Set wsSumm = ThisWorkbook.Worksheets("Summary")
    Set wsVis = ThisWorkbook.Worksheets("Visual")
    Set range_to_filter = wsSumm.Range("A3:Z113")
    Set range_to_filter_2 = wsVis.Range("B80:T109")
    
    If Not Application.Intersect(Me.Range("RegionChoice"), Target) Is Nothing Then

        Select Case Me.Range("RegionChoice").Value
            Case Me.Range("A1").Value: crit = "C"
            Case Me.Range("A2").Value: crit = "S"
            Case Me.Range("A3").Value: crit = "W"
            Case Me.Range("A4").Value: crit = "NE"
            Case Me.Range("A5").Value: crit = ""
            Case Else: crit = ""
        End Select
        
        ApplyFilter range_to_filter, 4, crit
        ApplyFilter range_to_filter2, 5, crit
    End If
        
    If Not Application.Intersect(Me.Range("ProjectType"), Target) Is Nothing Then

        Select Case Me.Range("ProjectType").Value
            Case Me.Range("A9").Value: crit = "Refresh"
            Case Me.Range("A10").Value: crit = "Buildout"
            Case Me.Range("A11").Value: crit = "Maintenance"
            Case Me.Range("A12").Value: crit = "Expansion"
            Case Me.Range("A13").Value: crit = "Furniture"
            Case Me.Range("A14").Value: crit = ""
            Case Else: crit = ""
        End Select
        
        ApplyFilter range_to_filter_2, 2, crit
    End If
            
End Sub

'Apply a filter to a range `rng`, using field number `fldNum`
Sub ApplyFilter(rng As Range, fldNum As Long, crit As String)
    If Len(crit) > 0 Then
        rng.AutoFilter Field:=fldNum, Criteria1:=crit
    Else
        rng.AutoFilter Field:=fldNum
    End If
End Sub