Set a filter in a table depending on content of cell

[fxmu]

Hi All,

I am trying to set a filter depending on the content of a cell. I have uploaded an example for clarification.

I am aiming to enter a number in cell b2. The number represents text in column a within the table. When changing B2 it would be great if VBA code could change the filter setting as stated in matrix e2:g4.

That's what I got so far:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("B2") Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=Range("B2")
    End If
End Sub

Please could you help me improve the code as described?

Many thanks.

[Richard Buttrey]

Hi,

Without the workbook I'm struggling to understand what you mean, and in particular the reference to the matrix e2:g4. Please upload the file

[fxmu]

The example should be uploaded.

[fxmu]

Any suggestions?

[Richard Buttrey]

Hi,

One way

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lSelection As Long, stFilter As String
    If Target = Range("B2") Then
        lSelection = Range("B2")

        Select Case lSelection
        Case Is = 1
            stFilter = "*"

        Case Is = 2
            stFilter = "aaa"

        Case Is = 3
            stFilter = "bbb"

        Case Else
            'default code

        End Select
        
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=stFilter
    End If
End Sub

[fxmu]

This works perfect. But I wasn't completely honest. The qualifiers 1, 2 and 3 are not produced through picking it from a list. Unfortunately they are produced through code. For example if scenario is x then B2 = 1. Somehow it is not working when 1 or 2 or 3 are created through code. Is there a way to solve this?

[Richard Buttrey]

Hi,

Yes, it's important to mention all salient points otherwise we all end up wasting some of our time.

You have chosen to use the sheet change event. Therefore you need to tell me what does causes B2 to change.
Is B2 a formula that's dependent on a precedent cell, or is B2 changed by a macro.

If the former then use the precedent cell in the current "Target = Range("B2")" instruction instead of B2. If the latter then adapt the Select Case code to reflect whatever it is in your macro that changes B2 - although unless you have disabled events, a macro that changes B2 would trigger the code I gave you anyway.

[fxmu]

Thanks and apologies for being not precise. The cause of the B2 change is a formula.

In the meantime I could harmonize some parts of the sheet. For calculation purposes I still need the number system but my choice field can contain "aaa + bbb", "aaa" and "bbb". So maybe the code can be adapted so that it doesn't need the numbers anymore.

The updated example 3 contains the same adapted data in sheets 1 and 2 and different VBA code

Sheet 1:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lSelection As Long, stFilter As String
    If Target = Range("B3") Then
        lSelection = Range("B3")

        Select Case lSelection
        Case Is = 1
            stFilter = "*"

        Case Is = 2
            stFilter = "aaa"

        Case Is = 3
            stFilter = "bbb"

        Case Else
            'default code

        End Select
        
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=stFilter
    End If
End Sub

Sheet 2:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lSelection As Long, stFilter As String
    If Target = Range("B2") Then
        lSelection = Range("B2")

        Select Case lSelection
        Case Is = "aaa + bbb"
            stFilter = "*"

        Case Is = "aaa"
            stFilter = "aaa"

        Case Is = "bbb"
            stFilter = "bbb"

        Case Else
            'default code

        End Select
        
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=stFilter
    End If
End Sub

But somehow it doesn't work on sheet 2 when I enter "aaa + bbb" instead of 1 ....

Still I am aiming the same - but it doesn't matter anymore if the filter is caused by the change of a number or text.

Your help is greatly appreciated.

[Richard Buttrey]

Hi,

The sheet1 macro won't work since you're using B3 as the target for the change event. This is often confusing but a formula which changes is not regarded as a sheet change event as far as VBA is concerned. Your drop down is B2 and that's the cell that triggers the macro. Hence the macro must use B2 in the If Target = Range("b2") Instruction. The Select case can of course use B3.

The sheet2 macro won't work for two reasons.

1 The lSelection variable is defined as a Long variable type whereas you are comparing strings in the Select Case statement. Hence the variable lSelection should be Dimmed as a String type

2. You are addressing a Table1 on sheet2 which doesn't exist. The table name on sheet2 is Table13. This is one reason why I prefer to use range names which can be the same name on different sheets.

However if you are wanting to use this code over several sheets and there is only one table on any of the sheets then you could use the Workbook_SheetChange event which means you don't then need any code in the Worksheet_Change events.
i.e.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim lSelection As String, stFilter As String
    If Target = Range("B2") Then
        lSelection = Range("B2")
        Select Case lSelection
        Case Is = "aaa + bbb"
            stFilter = "*"
        Case Else
            stFilter = lSelection
        End Select
        Sh.ListObjects(Sh.ListObjects(1).Name).Range.AutoFilter field:=1, Criteria1:=stFilter
    End If
End Sub

[fxmu]

This works well. Thanks very much.

[Richard Buttrey]

Glad to have helped and thanks for the rep.