Excel 2021 - Catching the variable from the Drop Down Filter

[BrerRabbit]

I've google this to no avail. The drop down filter, when you enter search criteria, is there anyway to catch this variable? I'm not looking to do a filter function with a separate table.

 

[kevin9999]

What do you mean by "catch this variable"? Catch it where exactly, and what do you intend to do with it after you've "caught" it?

 

[BrerRabbit]

What do you mean by "catch this variable"? Catch it where exactly, and what do you intend to do with it after you've "caught" it?

When a search criteria is entered into the search box in the dropdown list, i'd like to see if that search criteria can be copied into a cell as well.

I don't like my chances, but I am wondering if it has been done.

 

[kevin9999]

See if this does what you want. A number of steps to go through:
1. Place =CountA(A:A) on your sheet to enable the triggering of a Worksheet_Calculate event (you can hide it)
2. Put the following code in the sheet code area of the sheet you have the autofilter in (it's checking column B (2) change to suit, and putting the filter criteria into cell F1 - also change as needed)

Private Sub Worksheet_Calculate()
    With ActiveSheet.AutoFilter.Filters(2)                              '<-- checking column 2 - change to suit
        If .On Then
            Range("F1").Value = Right(.Criteria1, Len(.Criteria1) - 1)  '<-- copied to cell F1 - change to suit
        Else
            Range("F1").Value = ""
        End If
    End With
End Sub

With the following starting sheet:

auto criteria (version 2).xlsb
	A	B	C	D	E	F	G
1	hdr1	hdr2	hdr3		Search Value:		7
2	x	a	x
3	x	a	x
4	x	b	x
5	x	b	x
6	x	aa	x
7	x	bb	x
Sheet1

Cell Formulas
Range	Formula
G1	G1	=COUNTA(A:A)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$7	G1

Entering "a" as the autofilter criteria results in this:

auto criteria (version 2).xlsb
	A	B	C	D	E	F	G
1	hdr1	hdr2	hdr3		Search Value:	a	7
2	x	a	x
3	x	a	x
8
Sheet1

Cell Formulas
Range	Formula
G1	G1	=COUNTA(A:A)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$7	G1

or "bb" results in this:

auto criteria (version 2).xlsb
	A	B	C	D	E	F	G
1	hdr1	hdr2	hdr3		Search Value:	bb	7
7	x	bb	x
8
Sheet1

Cell Formulas
Range	Formula
G1	G1	=COUNTA(A:A)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$7	G1

Clearing the filter does this:

auto criteria (version 2).xlsb
	A	B	C	D	E	F	G
1	hdr1	hdr2	hdr3		Search Value:		7
2	x	a	x
3	x	a	x
4	x	b	x
5	x	b	x
6	x	aa	x
7	x	bb	x
8
Sheet1

Cell Formulas
Range	Formula
G1	G1	=COUNTA(A:A)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$C$7	G1

 

[BrerRabbit]

A couple of questions, if that's ok.

1. I'm assuming that any counta or countif or count will trigger the VB code. Is that correct?
2. I get this error message when trying to run the code.
RUN TIME ERROR 91
OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET

This is very cool btw.

 

[kevin9999]

I usually just use a CountA() but feel free to experiment.
What line are you getting the Run Time Error on?

 

[BrerRabbit]

I usually just use a CountA() but feel free to experiment.
What line are you getting the Run Time Error on?

This one: With ActiveSheet.AutoFilter.Filters(2)

It doesn't proceed beyond that point.

 

[kevin9999]

Is the AutoFilter switched on, and on column 2 (B)?