Set a filter in a table depending on content of cell

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:

Please Login or Register  to view this content.

Please could you help me improve the code as described?

Many thanks.

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

Hi,

One way

Please Login or Register  to view this content.

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.

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.

Please Login or Register  to view this content.

Glad to have helped and thanks for the rep.