Hi,
i wrote some code below.
a formula in a conditional format seems to be called when a cell
is repainted.
my idea is that making a volatile formula like
=SumHiddenRows(A1:A10)+NOW()*0
and setting an UDF in a format condition in the hidden/unhidden cells
(like '=CatchPaint()' in A1:A10), the hidden cell calls the UDF,
the UDF starts timer, the timer procedure calculates formulas.
i am not sure that this would work perfectly.
(i am testing this in excel 2000)

'Module1
Private Declare Function SetTimer Lib "user32" _
(ByVal hWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32.dll" _
(ByVal hWnd As Long, ByVal uIDEvent As Long) As Long

Private TimerId As Long

Private Function TimerProc(ByVal hWnd As Long, ByVal uMsg As Long, _
ByVal idEvent As Long, ByVal dwTime As Long) As Long

On Error Resume Next
KillTimer 0, idEvent

ActiveSheet.Calculate
'ActiveSheet.Range("A1").Calculate
'Application.Calculate 'this interferes with entering formula in xl2k

TimerId = 0
End Function

Private Function CatchPaint()
If TimerId = 0 Then TimerId = SetTimer(0, 0, 0, AddressOf TimerProc)
End Function

--
Regards,

okaizawa


Peter Rooney wrote:
Good morning all!

I wrote a simple function to calculate the values of all hidden cells in a
range.
It works fine, except that I have to edit the formula with [F2] and [Enter]
before it displays the correct answer - otherwise, it just displays the
result of the previous calculation.

Can anyone help, please?

Thanks in advance

Pete

Function SumHiddenRows(TheHiddenRange)
Dim HiddenTotal As Long
Dim HiddenCell As Range

HiddenTotal = 0
For Each HiddenCell In TheHiddenRange
If HiddenCell.EntireRow.Hidden = True Then
HiddenTotal = HiddenTotal + HiddenCell.Value
End If
Next
SumHiddenRows = HiddenTotal
Calculate
End Function