Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full recalculation when opening a workbook
I would like to trigger full re-calculation (equivalent to CTRL-ALT-F9
or Application.CalculateFull) whenever I load a new workbook. I have Excel 97 SR-2, so CalculateFull is not available to me. I am working on a project where at present I need to manually do a CTRL-ALT-F9 whenever I open an existing sheet, so I would like to automate this. I have created an add-in with the following procedure defined in the workbook vba Private Sub Workbook_Open() Application.SendKeys ("^%{F9}") DoEvents Debug.Print ("Sent keys") End Sub Now I can see the message printed so the code has been excuted, but there is no effect on the sheet : I still need to manually do CTRL-ALT-F9 before the sheet is in the state I require. The question is: why didn't the sheet receive the message to re-calculate and what can I do to fix this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full recalculation when opening a workbook
Not sure if you can use the following in xl97 ?
Private Sub Workbook_Open() Application.Calculate End Sub -- Cheers Nigel "Brian Hall" wrote in message ... I would like to trigger full re-calculation (equivalent to CTRL-ALT-F9 or Application.CalculateFull) whenever I load a new workbook. I have Excel 97 SR-2, so CalculateFull is not available to me. I am working on a project where at present I need to manually do a CTRL-ALT-F9 whenever I open an existing sheet, so I would like to automate this. I have created an add-in with the following procedure defined in the workbook vba Private Sub Workbook_Open() Application.SendKeys ("^%{F9}") DoEvents Debug.Print ("Sent keys") End Sub Now I can see the message printed so the code has been excuted, but there is no effect on the sheet : I still need to manually do CTRL-ALT-F9 before the sheet is in the state I require. The question is: why didn't the sheet receive the message to re-calculate and what can I do to fix this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full recalculation when opening a workbook
No, that does not 'force' my sheet to recalculate. From xl2000 on there
is Application.CalculateFull, but my understanding is that CTRL-ALT-F9 is equivalent in xl97 Nigel wrote: Not sure if you can use the following in xl97 ? Private Sub Workbook_Open() Application.Calculate End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full recalculation when opening a workbook
Hi Brian,
try this Dim oSht as worksheet Application.Calculation=xlCalculationManual for each oSht in Worksheets oSht.enablecalculation=false osht.enablecalculation=true next osht Application.calculate Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Brian Hall" wrote in message ... No, that does not 'force' my sheet to recalculate. From xl2000 on there is Application.CalculateFull, but my understanding is that CTRL-ALT-F9 is equivalent in xl97 Nigel wrote: Not sure if you can use the following in xl97 ? Private Sub Workbook_Open() Application.Calculate End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full recalculation when opening a workbook
Actually, I believe now that my problem is not where I was looking for
it. Remember that I want any .xls that is openned to be re-calculated once. By putting the SUB Workbook_open procedure in my .xla I was effectively telling Excel to recalculate only when the application openned (and hence the add-in workbook openned too). So, I now wonder: how do I put something in an add-in that will trap the event of an .xls being openned? I guess that the Application.WorkbookOpen event will be what I need. Thanks for the help though! Charles Williams wrote: Hi Brian, try this Dim oSht as worksheet Application.Calculation=xlCalculationManual for each oSht in Worksheets oSht.enablecalculation=false osht.enablecalculation=true next osht Application.calculate Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Brian Hall" wrote in message ... No, that does not 'force' my sheet to recalculate. From xl2000 on there is Application.CalculateFull, but my understanding is that CTRL-ALT-F9 is equivalent in xl97 Nigel wrote: Not sure if you can use the following in xl97 ? Private Sub Workbook_Open() Application.Calculate End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full recalculation when opening a workbook
Hi Brian
See this article on setting up application level events. I don't know for sure if this will work in 97 but it could be the answer to your problem. If you implement application level events in your xla you can then trap the workbook_open event and execute your sendkeys operation everytime any workbook is opened. http://www.cpearson.com/excel/AppEvent.htm Hope this helps Rowan Brian wrote: Actually, I believe now that my problem is not where I was looking for it. Remember that I want any .xls that is openned to be re-calculated once. By putting the SUB Workbook_open procedure in my .xla I was effectively telling Excel to recalculate only when the application openned (and hence the add-in workbook openned too). So, I now wonder: how do I put something in an add-in that will trap the event of an .xls being openned? I guess that the Application.WorkbookOpen event will be what I need. Thanks for the help though! Charles Williams wrote: Hi Brian, try this Dim oSht as worksheet Application.Calculation=xlCalculationManual for each oSht in Worksheets oSht.enablecalculation=false osht.enablecalculation=true next osht Application.calculate Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Brian Hall" wrote in message ... No, that does not 'force' my sheet to recalculate. From xl2000 on there is Application.CalculateFull, but my understanding is that CTRL-ALT-F9 is equivalent in xl97 Nigel wrote: Not sure if you can use the following in xl97 ? Private Sub Workbook_Open() Application.Calculate End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Full recalculation when opening a workbook
That article was written when xl97 was the latest version.
-- Regards, Tom Ogilvy "Rowan" wrote in message ... Hi Brian See this article on setting up application level events. I don't know for sure if this will work in 97 but it could be the answer to your problem. If you implement application level events in your xla you can then trap the workbook_open event and execute your sendkeys operation everytime any workbook is opened. http://www.cpearson.com/excel/AppEvent.htm Hope this helps Rowan Brian wrote: Actually, I believe now that my problem is not where I was looking for it. Remember that I want any .xls that is openned to be re-calculated once. By putting the SUB Workbook_open procedure in my .xla I was effectively telling Excel to recalculate only when the application openned (and hence the add-in workbook openned too). So, I now wonder: how do I put something in an add-in that will trap the event of an .xls being openned? I guess that the Application.WorkbookOpen event will be what I need. Thanks for the help though! Charles Williams wrote: Hi Brian, try this Dim oSht as worksheet Application.Calculation=xlCalculationManual for each oSht in Worksheets oSht.enablecalculation=false osht.enablecalculation=true next osht Application.calculate Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Brian Hall" wrote in message ... No, that does not 'force' my sheet to recalculate. From xl2000 on there is Application.CalculateFull, but my understanding is that CTRL-ALT-F9 is equivalent in xl97 Nigel wrote: Not sure if you can use the following in xl97 ? Private Sub Workbook_Open() Application.Calculate End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disabling formula recalculation when opening or closing workbooks | Excel Discussion (Misc queries) | |||
Ctrl+Alt+F9 not performing Full Recalculation on some PCs | Excel Worksheet Functions | |||
Opening in Full Screen. | New Users to Excel | |||
"Disk is full" error when opening Excel 2000. | Excel Discussion (Misc queries) | |||
Prevent recalculation when opening earlier versions | Excel Programming |