エクセルの特定シートを完全に手動計算にする方法

質問

こんにちは。
エクセルのVBAについて最近勉強を始めた初心者です。
下記について知恵をお借りしたく、質問させて頂きます。

４つのシートで構成されているブックがあります。（他ブックとの連動はありません）
シート１は集計表で、シート２～４はシート１の元データーが入っています。
本体はシート１のみ手動計算にしたいのですが、現状は以下の方法を取っています。

(1)ブックを開くと、ブック全体を手動計算にする
(2)必要なタイミングで、シート１の特定セル範囲（３パターンあり）を再計算させるマクロを実行
(3)ブックを閉じると、自動計算に戻す

こちらの方法でほぼ問題無いように思えたのですが、
ブックを閉じる時にエクセルの仕様で『'ファイル名.xls'への変更を保存しますか？』と聞かれますよね。
そこで"キャンセル"を選択すると、ブックは開いたまま自動計算モードに戻る＝シート１が再計算されてしまうのです。
（このタイミングで(3)（Workbook_BeforeClose）のコードが走っているのだと思います）

先述の通り、本来手動計算にしたいのはシート１に限定されており、
シート２～４は自動計算で構いません。

やりたい事をまとめると、
■ブックを開く　 ：シート１=手動計算、シート２～４＝自動計算
■ブックを閉じる：シート１＝自動計算に戻す、ただしキャンセルした場合は手動計算モードを保持

現在のWorkbook_BeforeClose～の部分に何らかのコードを加えたら実現可能？
とも思いますが、そこが分からず行き詰っています…。

上記実現するために良い方法がありましたら、ご教示お願いいたします。

【This Wook Book】に記述しているコード-----------------------

Private Sub Workbook_Open()
'ブックを開くと手動計算にする
Application.Calculation = xlCalculationManual
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ブックを閉じると自動計算に戻す
Application.Calculation = xlCalculationAutomatic
End Sub

-----------------------------------------------------

回答No.3

doara_2011 · ベストアンサー

こんな感じでどうでしょう？

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　Dim ans As VbMsgBoxResult

　　If Not ThisWorkbook.Saved Then
　　　　' 保存されていない場合、ダイアログを出力する。
　　　　ans = MsgBox("'" & ThisWorkbook.Name & "'の変更を保存しますか？", _
　　　　　　　vbExclamation + vbYesNoCancel + vbDefaultButton3)
　　　　Select Case ans
　　　　Case vbYes　' はいを選択した場合
　　　　　　Cancel = False
　　　　　　ThisWorkbook.Save　　' ここでブックの保存を行う。
　　　　Case vbNo　 ' いいえを選択した場合
　　　　　　Cancel = False
　　　　　　ThisWorkbook.Saved = True ' 保存したことにする
　　　　Case vbCancel　　' キャンセルを選択した場合
　　　　　　Cancel = True　　　' 閉じるのをキャンセルする。
　　　　End Select
　　Else
　　　　' 保存されていればそのままブックを閉じる
　　　　Cancel = False
　　End If

　　' ブックを閉じる事を選択した場合のみ自動計算モードにする。
　　If Not Cancel Then Application.Calculation = xlCalculationAutomatic
End Sub

VBAでエクセルの出す「保存しますか？」の問いに答えた結果を取得することは
できませんが、その問いの出力される条件はわかってますので、それに似た
ダイアログを自前で出力し、答えた結果に従って分岐させているだけです。
なお、マクロはブラウザでの見やすさ優先のため、行頭には全角のスペースを
使用しています。

以上

補足

お返事が大変遅くなり申し訳ありません。
教えて頂いた方法を検証したところ、ほぼ希望通りの結果になりました。
ただ、”擬似ダイアログ”をキャンセル→ブックを上書き保存→”本物ダイアログ”をキャンセル、とするとやはり自動計算に戻ってしまいました。
これはもうエクセルの仕様なのかな思います。

教えて頂いた方法を元に、もう試行錯誤してみます。
大変勉強になりましたどうもありがとうございました。

回答No.2

imogasi

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet1" Then
MsgBox "手動計算セット 適時F9を押せ"
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomatic
End If
End Sub
十分テストして無いが、どうだろう。

補足

回答ありがとうございます。

教えて頂いたコードでテストしてみましたが、
他のシートに移動した際に再計算されてしまいました。

質問文の通りシート２～４にはシート１の元データが入っています。
元データは随時書き換えられます。

シート１はアクティブ・非アクティブに関わらず常に手動計算モードにしておき、
再計算するのはマクロ実行したときのみ、という形が理想なのですが…。

回答No.1

MackyNo1

シートモジュールで対象シートを選択したときに「手動」にして、他のシートに移動したときに自動計算するのではダメでしょうか？

Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
End Sub

Private Sub Worksheet_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub

補足

＞シートモジュールで対象シートを選択したときに「手動」にして、他のシートに移動したときに自動計算するのではダメでしょうか？

回答ありがとうございます。
対象のシートはマクロ実行でのみ再計算させたいので、
他のシートに移動したときに再計算されては困るのです…。
（ブックを開いた時から閉じる時まで手動計算にしたいです）