Re : Excel FilterMode to Trigger WorkSheet_Calculate()

[tkt_tang]

1. There are 2 worksheets (Sheet1 & Sheet2) in an Excel workbook.

2. The given worksheets are similar in that both of them contain
AutoFilters and make use of Event-Handlers such as WorkSheet_Calculate
().

3. On Sheet1, it enters into FilterMode after any one of the
AutoFilters is activated and consequently, WorkSheet-Calculate() is
Triggered.

4. On Sheet2, it enters into FilterMode after any one of the
AutoFilters is activated ; however, WorkSheet-Calculate() is NOT
Triggered.

5. The question is, Why is that difference between Sheet1 and Sheet2
with respect to Trigger & Non-Trigger of WorkSheet-Calculate() ?

6. Please share your experience.

7. Regards.

 

[Jacob Skaria]

I see 2 reasons.

1. Out of the below check where you have written your code.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'This Workbook event will be triggered for all sheets
End Sub

Private Sub Worksheet_Calculate()
'Each individual sheet has got its own Calculate event which will be
triggered only for that sheet
End Sub

2. Check the sheet reference used in your code. Check whether you have
specified ActiveSheet or Sheets(1) or ...

If this post helps click Yes

 

[tkt_tang]

Jacob Skaria, Esq

Thank you very much indeed.

Have used ActiveSheet for the "Code-Behind" of Sheet1 & Sheet2.

As it turns out, Sheet2 does not execute the on-board "Code-Behind's
Worksheet_Calculate()" ; it makes use of Sheet1's "Code-Behind".
That's rather Odd indeed.

Have not made use of WorkBook's SheetCalculate().

Other lessons learned are that WorkSheet_Calculate()'s could execute
one after the other for the WorkSheets' Code-Behind.

WorkSheet_Calculate()'s can be rather difficult to handle.

Please continue discussion.

Regards.

 

[Jacob Skaria]

If you try a simple formula and calculate in all three sheets it calls the
corresponding WorkSheet_Calculate()

May be autofilter is not doing any calculations at all....instead use
Selection change or Change event. If you work with Workbook from sh object
you can get sh.name or sh.index which gives you the worksheet name or index
number


If this post helps click Yes