Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
IF Function to test formula in a cell | Excel Worksheet Functions | |||
copying the function contained within a cell to anouther cell. | Excel Worksheet Functions | |||
Function syntax to compare cell contents | Excel Worksheet Functions | |||
SQL - Auto Refresh Upon Cell Value Change | Excel Worksheet Functions |