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


On 06/29/2010 09:25, jodleren wrote:
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