WorksheetsEnableCalculation

H

Hugh

Help the village idiot here.
Am trying to keep a large worksheet from calculating with the other
sheets in the workbook.
Worksheet named "Bob" has no cells in other sheets that are dependant on
it. It simply collects data & does calculations.
I have this little code for "Bob"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Bob").EnableCalculation = True
End Sub

and this for the other sheets:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Bob").EnableCalculation = False
Worksheets("Ted").EnableCalculation = True
End Sub

if i go to "Bob" and calculate, then go to any other sheet, the first
time i hit enter, it recalculates every thing, including "Bob".
Subsequent entries do not. Why?

Related question - is there any way to do this little bit of code so i
don't have to edit it every time a sheet is added or deleted?
 
F

Fredrik Wahlgren

Hugh said:
Help the village idiot here.
Am trying to keep a large worksheet from calculating with the other
sheets in the workbook.
Worksheet named "Bob" has no cells in other sheets that are dependant on
it. It simply collects data & does calculations.
I have this little code for "Bob"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Bob").EnableCalculation = True
End Sub

and this for the other sheets:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets("Bob").EnableCalculation = False
Worksheets("Ted").EnableCalculation = True
End Sub

if i go to "Bob" and calculate, then go to any other sheet, the first
time i hit enter, it recalculates every thing, including "Bob".
Subsequent entries do not. Why?

Related question - is there any way to do this little bit of code so i
don't have to edit it every time a sheet is added or deleted?

Excel does not remember how a workbook has been calculated. The first time
you hit enter, excel has to build some kind of dependency tree. The second
time is different, now excel knows that no other sheet depends on the "Bob"
sheet.

/Fredrik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top