Customized function (in cell) does not refresh value :o(

My workbook is built dynamically... I have N number of sheets and ONE summary
sheet where I want to calculate the sum (for the same cells address) of the
others. By example...

If I have this...
Sheet 1 - Range("A1") = 500
Sheet 2 - Range("A1") = 400

I want SumSheets() function return 900...
Summary Sheet - Range("A1") = SumSheets(ligne();colonne())

That works. But my problem is the value is not refreshed after I change
something in the Sheet 1 or Sheet 2. I tried to place a calculate method in
activate event of the summary sheet, but nothing change. Why the call of the
function is not trigger? And is it a better way to do what I need?
Thank you!

Hi Marc,
Is your SumSheets() function volatile?
To make it volatile you can add the following single line of code...

Application.Volatile

Make it the first line of code after the Function SumSheets() top line.

Hope this helps.

Ken Johnson

Hi Ken,
Thank you for your answer :o)
Yes, I tried with Application.Volatile but it does not work. I also tried to
put a Activesheet.Calculate in the Worksheet_Activate event, but nothing
works... except if I put Application.CalculateFull in the Worksheet_Activate
event... but it's not what I want... to recalculate all open workbooks :o(
Another alternative?

Ken Johnson wrote:
>Hi Marc,
>Is your SumSheets() function volatile?
>To make it volatile you can add the following single line of code...
>
>Application.Volatile
>
>Make it the first line of code after the Function SumSheets() top line.
>
>Hope this helps.
>
>Ken Johnson

It works now. I put a both commands...
Application.Volatile at the top of my custom function
and ActiveSheet.Calculate in the Worksheet_Activate event of the summary
sheet.
The Calculate without the Volatile did not work every time. It seems it works
only at first time.

MarcL wrote:
>Hi Ken,
>Thank you for your answer :o)
>Yes, I tried with Application.Volatile but it does not work. I also tried to
>put a Activesheet.Calculate in the Worksheet_Activate event, but nothing
>works... except if I put Application.CalculateFull in the Worksheet_Activate
>event... but it's not what I want... to recalculate all open workbooks :o(
>Another alternative?
>
>>Hi Marc,
>>Is your SumSheets() function volatile?
>[quoted text clipped - 7 lines]
>>
>>Ken Johnson