AutoFilter Trigger Calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sam - 22 May 2008 19:04 GMT

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

Reply to this Message

Norman Jones - 22 May 2008 19:55 GMT

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"))

---
Regards.
Norman

> Hi All,
>
[quoted text clipped - 8 lines]
> Thanks,
> Sam

Reply to this Message

Sam - 23 May 2008 02:16 GMT

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
[quoted text clipped - 3 lines]
>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"))

>---
>Regards.
>Norman

Reply to this Message

Norman Jones - 23 May 2008 09:41 GMT

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,
>
[quoted text clipped - 41 lines]
>>Regards.
>>Norman

Reply to this Message

Sam - 23 May 2008 16:31 GMT

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

>---
>Regards.
>Norman

Reply to this Message