AutoFilter Trigger Calculation
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

AutoFilter Trigger Calculation
 

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


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:848b08e9b864e@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

AutoFilter Trigger Calculation
 

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

Norman Jones wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

AutoFilter Trigger Calculation
 

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

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:848ecd6943aa6@uwe...
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

Norman Jones wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1

AutoFilter Trigger Calculation
 

Hi Norman,

Thank you for your time and assistance.

Norman Jones wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1