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! |
Customized function (in cell) does not refresh value :o(
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 |
Customized function (in cell) does not refresh value :o(
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 |
Customized function (in cell) does not refresh value :o(
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 |
All times are GMT +1. The time now is 02:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com