okaizawa wrote:
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

This won't work i'm afraid, the calculate function won't calculate the
relevant cell because excel 2000 and lower doesn't refresh its
calculation tree on hide/unhide events.