Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 autofilter change to 2003 autofilter functionality? | Excel Discussion (Misc queries) | |||
Using a month as a trigger to do a calculation | Excel Discussion (Misc queries) | |||
Using autofilter to trigger an event | Excel Programming | |||
add text in a cell to trigger a calculation | Excel Worksheet Functions | |||
MOREFUNC.XLL - RECALL() to trigger once only calculation? | Excel Discussion (Misc queries) |