Calculate Workbook on Worksheet open_event(s)

  • Thread starter Thread starter javaeboy
  • Start date Start date
J

javaeboy

I have a workbook with about 65 worksheets in it. Because of some o
the items the people have been gracious enough to help me wit
(primarily the INDIRECT function to dynamically call worksheets) I hav
a workbook that takes forever to recalculate on the antique computers w
have at work.

What I would like to do, is set the workbook itself to manua
recalculation, BUT, have a macro or whatever run, ONLY when a differen
worksheet is selected. Kind of a recalculate ONLY on the Open event o
each worksheet.

This has to work in a shared environment. I know there has to be som
kind of code or macro I can apply to each worksheet, but what is th
code and where do I put it. (I may even need a clue as to how to ge
to where to put it.)

One last tidbit, I need it to work in an environment where not ever
copy of Excel has the plug-ins installed. The IT techs would crucif
me if I asked them to update all these machines with the add-ins...

Also, it should calculate on SAVE also (I think that will be automati
though... Am I correct in assuming this?)

Thank you in advance for all your help now and in the past..
 
Copy/paste these 3 macros into the ThisWorkbook code module. They work
automatically.

(key Alt +F11 to get the VB Editor. Ctrl +R to get the Project Browser.
Douleclick Thisworkbook)


Code:
--------------------

'-----------------------------------------------------
'- set to manual on opening
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
End Sub
'-----------------------------------------------------
'- calculate single sheet when sheet changed
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Calculate
End Sub
'-----------------------------------------------------
'- set to automatic on closing
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
End Sub
'-----------------------------------------------------

--------------------
 
Brian, Thank you for the possible solution. I tried it while I was a
work (I had forgotten my password into this forum, and couldn't writ
back) It did part of what I needed, but not really the extent of what
wanted to do. It does set the calculation to manual when I open th
workbook, and it does recalculate on save. What I need it to do i
autocalculate when I open a different worksheet than the one I open th
workbook on.

Eg. I am entering my sales information in worksheet "javaeboy", (
have 60 of these worksheets for the various employees) I have anothe
worksheet that evaluates and does some calculations (puts things i
their approriate columns and does some sumations) on a worksheet calle
"MonthToDate". I don't want it to run the various embedded formula
and calculations on worksheet "javaeboy" until I click-on (open)
different worksheet eg. "MonthToDate". There may be 20 people or mor
entering their sales information on their specific sheets and I don'
want the calculations to slow everyones entries. As they are enterin
information, each cell they are entering is an argument in a formul
that is being evaluated on a few other worksheets, like the MonthToDat
worksheet.

Is that supposed to happen with your code? Do I need to put an entr
in some event(s) of each worksheet? I appreciate your help. Thank yo
(or anyone else) in advance
 
The instructions are there. just copy & paste all the code int
Thisworkbook.

Calculation is set to Manual and calculation of a single sheet occur
whenever another worksheet is activated
 
BrianB,

I don't know what I am doing wrong. I have pasted your code into th
place you told me, and now nothing is happening correctly. I hav
written a small 2 sheet version that shows my problem. I have attache
it. I have 2 sheets, Sheet 1 is where you can enter a number in B1 an
text in B2. There is a concatenation of B1 & B2 in Cell B4. I also
have the same formula in cell B1 on Sheet 2. According to your cod
and reasoning, Cell B4 on Sheet 1 should not calculate unless I clic
on Sheet 2. What I am seeing is that B4 is calculating anyways. If
manually set the calculation to Manual, it won't calculate unless an
ONLY if I hit F9 on either sheet. I had the manual calculation an
auto calculation working on my version at work, but I can't seem to ge
it to work here. I have closed Excel completely and reopened the shee
with the same results each time. If I set it to Automatic Recalc an
close the workbook, and then reopen Excel and this workbook, I woul
expect your code would set it to manual recalc. I am soooo confused.
Thank you again. See if you can see what I may have done wrong...
 
BrianB,

I don't know what I am doing wrong. I have pasted your code into th
place you told me, and now nothing is happening correctly. I hav
written a small 2 sheet version that shows my problem. I have attache
it. I have 2 sheets, Sheet 1 is where you can enter a number in B1 an
text in B2. There is a concatenation of B1 & B2 in Cell B4. I also
have the same formula in cell B1 on Sheet 2. According to your cod
and reasoning, Cell B4 on Sheet 1 should not calculate unless I clic
on Sheet 2. What I am seeing is that B4 is calculating anyways. If
manually set the calculation to Manual, it won't calculate unless an
ONLY if I hit F9 on either sheet. I had the manual calculation an
auto calculation working on my version at work, but I can't seem to ge
it to work here. I have closed Excel completely and reopened the shee
with the same results each time. If I set it to Automatic Recalc an
close the workbook, and then reopen Excel and this workbook, I woul
expect your code would set it to manual recalc. I am soooo confused.
Thank you again. See if you can see what I may have done wrong...

Attachment filename: test_for_brianb.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65059
 
The first thing you should try is a workbook with *only* the code I hav
supplied to make sure that is working. Then add sections and retest
Hopefully you will find the part that is not working correctly
 
BrianB, Thanks for all your help. That is why I sent a mini version o
what I was seeing. I have it figured out. I changed the Sh.Calculat
to a Application.Calculate in the Workbook_SheetActivate method and i
seemed to work correctly. It is deceiving though, the workboo
calculations sometimes take so long to get to the sheet you jus
activated, that you don't realize the automatic calculations are takin
place. Quick question. On a workbook recalculation, do the sheet
recalculate in any particular order? Sheet1, Sheet2, Sheet3... or i
there an algorithm to control that
 
Back
Top