Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetCalculate fires all the time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetCalculate fires all the time
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetCalculate fires all the time
On Jun 29, 8:20*pm, Dave Peterson wrote:
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: *Time to 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetCalculate fires all the time
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!) On 06/29/2010 14:57, jodleren wrote: On Jun 29, 8:20 pm, Dave wrote: 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: Time to 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SheetCalculate Event | Excel Programming | |||
SetLinkOnData only fires once? | Excel Programming | |||
Which Fires First? | Excel Programming | |||
SheetCalculate (F9) does not reach the excel-addin | Excel Programming | |||
Problem with SheetCalculate Event | Excel Programming |