AutoFilter Trigger Calculation

[Sam via OfficeKB.com]

Hi All,

I would like to trigger a calculation of the active worksheet when a specific
AutoFilter Field is selected and any criteria / item is filtered or reset to
AutoFilter All. Can anyone provide the relevant VBA code and advise where it
should be placed?

Thanks,
Sam

 

[Norman Jones]

Hi Sam,

I do not think that any events are directly
triggered changes in an Autofilter's criteria.

The conventional aprroach would be to use
Excel's SubTotal formula (see below) in a
suitable location on the worksheet; the
Worksheet_Calculate event would then be
fired by a change in the value returned by the
formula, which wuld result from a change to
the autofilter criteria.

The relevant formula might be something like:

Application.WorksheetFunction.Subtotal(2, Range("A2:A1000"))

 

[Sam via OfficeKB.com]

Hi Norman,

Thank you very much for reply.

Apologies for late reply to Post but I 've been trying variations of your
suggested formula below.
I do have a SUBTOTAL Formula on the worksheet but the workbook is set to
manual calculation.
In the Private Sub Worksheet_Calculate() I've tried Worksheets("Sheet1").
EnableCalculation = True but it does not make any difference.

The relevant formula might be something like:
Application.WorksheetFunction.Subtotal(2, Range("A2:A1000"))

I've also tried the above with my range in the Private Sub
Worksheet_Calculate() but it expects an assignment of some sort. I declared a
variable as an integer and assigned the above calculation to it and then
unsuccessfully TRIED to pass that to a range on my worksheet.

Any further help is very much appreciated.

Cheers
Sam

Hi Sam,

I do not think that any events are directly
triggered changes in an Autofilter's criteria.

The conventional aprroach would be to use
Excel's SubTotal formula (see below) in a
suitable location on the worksheet; the
Worksheet_Calculate event would then be
fired by a change in the value returned by the
formula, which wuld result from a change to
the autofilter criteria.

The relevant formula might be something like:

Application.WorksheetFunction.Subtotal(2, Range("A2:A1000"))

 

[Norman Jones]

Hi Sam,

In order to utilise the Worksheet_Calculate event,
it is necessary to enable automatic calculation.

Trying to enable automatic calculation from the
calculate event will not work, because the event
will not be triggered unless automatic calculation is
enabled.

Consequently, try setting calculation to automatic;
if you have overridinng reasons for maintaining the
manual calculation mode, my suggestion will not be
appropriate.



---
Regards.
Norman

Hi Norman,

Thank you very much for reply.

Apologies for late reply to Post but I 've been trying variations of your
suggested formula below.
I do have a SUBTOTAL Formula on the worksheet but the workbook is set to
manual calculation.
In the Private Sub Worksheet_Calculate() I've tried Worksheets("Sheet1").
EnableCalculation = True but it does not make any difference.

The relevant formula might be something like:
Application.WorksheetFunction.Subtotal(2, Range("A2:A1000"))

I've also tried the above with my range in the Private Sub
Worksheet_Calculate() but it expects an assignment of some sort. I
declared a
variable as an integer and assigned the above calculation to it and then
unsuccessfully TRIED to pass that to a range on my worksheet.

Any further help is very much appreciated.

Cheers
Sam

Hi Sam,

I do not think that any events are directly
triggered changes in an Autofilter's criteria.

The conventional aprroach would be to use
Excel's SubTotal formula (see below) in a
suitable location on the worksheet; the
Worksheet_Calculate event would then be
fired by a change in the value returned by the
formula, which wuld result from a change to
the autofilter criteria.

The relevant formula might be something like:

Application.WorksheetFunction.Subtotal(2, Range("A2:A1000"))

 

[Sam via OfficeKB.com]

Hi Norman,

Thank you for your time and assistance.

Hi Sam,

In order to utilise the Worksheet_Calculate event,
it is necessary to enable automatic calculation.

Trying to enable automatic calculation from the
calculate event will not work, because the event
will not be triggered unless automatic calculation is
enabled.

Consequently, try setting calculation to automatic;
if you have overridinng reasons for maintaining the
manual calculation mode, my suggestion will not be
appropriate.

Unfortunately, I do need the calculation mode set to manual.

If anything else comes to mind, please advise.

Cheers,
Sam