Multiple Select Case Statements in a Single Procedure

0 votes

Using Select Case statements and Autofilters in a single function, I'm attempting to Autofilter two tables. I'm not sure when it is ideal to utilise modules vs. worksheets, therefore I'm wondering whether this is my problem that these are running from the worksheet itself rather as a separate module.

RegionChoice and ProjectType are two discrete dropdown fields that are the targets of two independent Select Case statements. The RegionChoice-based autofilters (the first of these select case statements) function flawlessly. The filters in the second Select Case statements, however, don't seem to function at all.

Both tables should be affected by a filter based on RegionChoice, but only one table should be affected by a filter based on ProjectType. Thanks and please refer to the code below.

'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

This was originally written as a series of if/else statements, but after receiving input from a SO user, I changed it to Select Case. I'd prefer to preserve it if it's possible because it has proven to be a far more effective approach.

I anticipated that the second Select Case statement using cell ProjectType would function similarly to the first using RegionChoice. In any case, the autofilter statements are not running.

Jan 24, 2023 in Others by Kithuzzz
• 38,010 points
180 views

1 answer to this question.

0 votes

Here's a slightly different structuring that 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
answered Jan 24, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How do I UPDATE from a SELECT in SQL server?

In SQL Server, it is possible to insert ...READ MORE

answered May 30, 2022 in Others by anisha
• 140 points
357 views
0 votes
1 answer

How can we UPDATE from a SELECT in an SQL Server

We can firstly use SELECT statement to fetch ...READ MORE

answered May 27, 2022 in Others by Avinash
• 240 points
3,947 views
0 votes
1 answer
0 votes
1 answer

How do I protect all worksheet in an Excel workbook with a single click?

VBA Code : Dim ws as Worksheet Dim pwd ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,420 points
510 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
875 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,181 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
479 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
730 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,546 views
0 votes
1 answer

Convert a single png file to jpg in vba

Try this code: Sub ConveretPNGToJpg() ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,420 points
1,140 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP