SheetCalculate fires all the time

[jodleren]

Hi all

I have this sheet, with a BOM to order, but it hides certain items.
Then for sorting, I need to hide those items again. I do that by the
last unlabeled coloums, which is X for hide.
So, when calculating, it will hide that.
It worked well until the new 2007 version of Excel - it seems to fire
every second or so
So it flickets all the time.

My code is this, and it fires, then relaxed for a second or so, then
fires again.

Any ideas how I can avoid that?

WBR
Sonnich

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim iHidden, iRow As Integer
Dim ws As Worksheet

If Worksheets("BOM").Cells(1, 28).Value = "" Then
Worksheets("BOM").Cells(1, 28).Value = "X" ' marked that we are
working

Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Worksheets("BOM")
' find plase to store hidden data
iHidden = 1
While .Cells(6, iHidden).Text <> ""
iHidden = iHidden + 1
Wend

iRow = 7
While .Cells(iRow, 1).Value <> ""
If .Cells(iRow, iHidden).Value <> "" Then
.Rows(iRow).Hidden = True
End If
iRow = iRow + 1
Wend
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Worksheets("BOM").Cells(1, 28).Value = "" ' free this function
End If
End Sub

 

[Dave Peterson]

First, I would use the worksheet event, not the workbook event.

I'd move the code from behind the ThisWorkbook module to the worksheet module
for BOM.

Second, I'm betting that you upgraded from xl2002 (or earlier) to xl2007. In
xl2002 (and below), hiding a row didn't cause a recalculation.

But xl2003 added some new arguments to the =subtotal() function. That now can
ignore rows hidden manually -- not just hidden by an autofilter.

So I'm guessing that each time your code hides a row, excel says: Time to recalc!

And off it goes again running your event code.

I'd try this (in that BOM worksheet module!):

Option Explicit
Private Sub Worksheet_Calculate()

Dim iHidden As Long
Dim iRow As Long

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

With Me

If .Cells(1, 28).Value = "" Then
.Cells(1, 28).Value = "X" ' marked that we are working

iHidden = 1
While .Cells(6, iHidden).Text <> ""
iHidden = iHidden + 1
Wend

iRow = 7
While .Cells(iRow, 1).Value <> ""
If .Cells(iRow, iHidden).Value <> "" Then
.Rows(iRow).Hidden = True
End If
iRow = iRow + 1
Wend

.Cells(1, 28).Value = "" ' free this function

End If
End With

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With

End Sub

 

[jodleren]

First, I would use the worksheet event, not the workbook event.

I'd move the code from behind the ThisWorkbook module to the worksheet module
for BOM.

Second, I'm betting that you upgraded from xl2002 (or earlier) to xl2007. In
xl2002 (and below), hiding a row didn't cause a recalculation.

But xl2003 added some new arguments to the =subtotal() function.  That now can
ignore rows hidden manually -- not just hidden by an autofilter.

dont tell me that.... I asume/hope that by default it reads them all,
otherwise I just got more work

So I'm guessing that each time your code hides a row, excel says:  Timeto recalc!

actually, it runs all the time - there is no clear reason for it to do
so.... it just stands there, runs, waits a second and then again...

And off it goes again running your event code.

I'd try this (in that BOM worksheet module!):

thx
will try

 

[Dave Peterson]

Take a look at excel's help for =subtotal().

Pay particular attention to formulas like:
=subtotal(9,a2:A10)
and
=subtotal(109,a2:a10)

Then try some experiments using autofiltering and manually hiding rows.

And I've never seen excel run anything all the time (ignoring application.ontime
procedures). But hiding any rows in code without disabling events will cause
the code to fire over and over and over.

Depending on how many rows are hidden and how many times the code is called,
that _calculate event could be firing lots and lots and lots of times. (It only
seems like forever!)