Link to home
Start Free TrialLog in
Avatar of Bill Golden
Bill GoldenFlag for United States of America

asked on

Forcing recalculation of cells on opening an Excel spreadheet

Through EE the following Excel formula was developed to display a portion of the filename in a cell

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+5, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-22)

Unfortunately, periodically, but not always, on opening the spreadsheet the cell displays an incorrect value until you click on the cell and hit Enter.

Is there a way to force recalculation of all formulas, or certain cells, to recalculate on opening?
Avatar of Professor J
Professor J

on the workbook open event you can place this line Application.Calculate
SOLUTION
Avatar of Lyubomir Dimitrov
Lyubomir Dimitrov
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bill Golden

ASKER

Professor, I am not sure what you mean by the term "on the workbook open event."
Lyubomir,  

I suppose this...

Worksheets(“YourSheetName”).Calculate Link

but I am not sure where you put the code. And, if it is VBA code that requires the name of the worksheet, that will not work since there are dozens of worksheets being used with different names.
please see attached video i recorded for you.

if to force calculate all sheets then Application.CalculateFull  

if you only want it for once sheet then instead of that above line put this   ThisWorkbook.Sheets("YourSheetName").Calculate  rename the Yoursheetname with actual sheet name
Video.mp4
I place the following...

Private Sub Workbook_Open()
Application.Calculate
End Sub

In Module1 section, Sheet1 section and ThisWorkbook section. No results.
Hi Bill,

did you see the video i uploaded?

you need to put the below, Not in the module but in the "ThisWorkbook" object  

Private Sub Workbook_Open()
Application.CalculateFull
End Sub
Yes, I did watch the video. Thanks.
Unfortunately, I feel the law of unintended consequences circling here.  
The cells in question, same number cell on each worksheet, display a value based, in part, on their respective TabName, in this case 01-30.
So if you close the workbook setting on Tab 19, when you re-open the workbook, all the cells in question are using 19 instead of their respective Tab numbers.
Can you please close the workbook then open it again and this time press F9 key and then see if the values are changing. If it does then I can recommend another solution and if does not then we may use another method
No, the values are not changing when the file is closed, re-opened and F9 is pressed.
Please replace the Application.CalculationFull with

 application.calculatefullrebuild
If you can tell me how to piece together the two following formulas

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+5,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-14)

and

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

they accomplish the task.
I'm sorry, I forgot to respond to your last suggestion

application.calculatefullrebuild

had no effect
hi,

have you tried send keys? https://stackoverflow.com/questions/9096636/excel-2010-select-each-cell-in-a-row-activate-them-one-by-one

you can loop your worksheets and cells where it is necessary, but basically with send keys you are clicking and hitting enter should work.

all the best,
Lyubo
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks again to both of you.
You are welcome Bill.

Was the calculation setup manual?
No, it was automatic, but my flaky formula was causing the problem.