ExcelVBA ファイルクローズ時にエラーとなる

質問

次の記述で、コマンドボタン2クリックで、ブックが1つならExcelを、複数なら現在のブックを
閉じる動作をします。
Excelのバージョンが2010なら問題ありませんが、2003や2007だと、複数ブックがある時に
「問題が発生したため・・・Excelを終了します」のメッセージがでます。

Private Sub CommandButton2_Click()

If Workbooks.Count > 1 Then
ThisWorkbook.Close SaveChanges:=True
Else
ThisWorkbook.Save
Application.Quit
End If

End Sub

これの対処方法を教えてください。

回答No.3

cj_mover · ベストアンサー

こんにちは。
＃２補足欄へのレスです。
確認が必要なことへのリターンや新たな情報
などが少なくて、応える（答える）力がなかなか湧きませんでした。
うまくリードしてあげられなくてゴメンナサイ。

> sheet1の例で言えることは、ModalのユーザフォームでCloseするとエラーになる、ということかなと
思いました。

そうではない、ことを説明する為に、『回避策２　　Modal版』を例として挙げたのですが、、、？

>>　つまりSheet1のイベントを実行中で、ブックを閉じることが出来ないのに
>>　ブックを閉じようとすると、エラーになります。
...
>> 抜けるべきプロシージャを抜けないとブックを閉じられない

私が提示したテストで確認して頂きたかったのは、
「ブックを閉じられる状態を用意しないと、workbook.Close メソッドは失敗する」
という一般的なことと、
「ブックを閉じられない状態での、workbook.Close メソッドのエラー」
が、今回ご質問で問題となっているエラー、と同じ種類のエラーなのではないですか？
ということです。
>> ...ご提示のプロシージャの記述の問題ではなく...
呼び出し側の問題である可能性が高い、ということの検証を目的としたのが＃２です。

質問当初とも違う、＃２補足欄とも違う、視点を、質問者さんが切り替えた上で
解決（まずは原因）を質問者さん自身が探らないと、難しそうですね。
エラーの原因は、公開されていない部分にあるのですから。

　場合によってはUserFormそのものをUnloadしないとworkbook.Closeできない
　ようなControls（外部オブジェクト）の構成（設定）になっているのかも知れません。

　オブジェクトを扱っていて解放しなければならないアクセスとかコネクションとか
　が原因でworkbook.Closeできないのかも知れません。

　イベントドリブンからサブルーチンのネストが深すぎるのかもしれません。

　Excel一般機能を使用中で、ユーザーからの応答を待っている状態なのかも知れません。

　ただ単にシートイベントなどでCancelし忘れている単純ミスかも知れません。

　...

「ブックを閉じられない状態」は結構あるような、、、、
# それでいて詳細で整理された文献がある訳でもなく、私としても得意な訳ではありませんが、、、
まず、原因を付きとめることですし、原因を付きとめる為に何をすべきか、
ということなのだと思います。

　　ご提示のプロシージャの記述、以外の記述や各種プロパティ設定などで
　　「ブックを閉じられない状態」を作り出している部分はないか
　　ひとつひとつ慎重に確認していってみてください。

原因のひとつひとつは＃２のテストの例のように単純なものが多いので、
例えば、既存のブックのコピーを作成して、
記述を少しずつ削除（構文エラーやコンパイルエラーにならないように注意）しながら
都度都度実行して、原因を絞り込んでゆくとか。
"切り分ける"作業が必要なのかも、です。

後書きになりますが、
> 「ブックを閉じられない状態での、workbook.Close メソッドのエラー」
> が、今回ご質問で問題となっているエラー、と同じ種類のエラー
であると "確認" できていれば、以上のような説明になりますが、
そうではない、ということなら、ただの無駄話になってしまいます。
ただ、解決への工程という意味では共通してる点も多いですけど。

次に補足があって、新たな局面を迎えて、私で役に立てるようなら、もう一度レスします。
今日は私も不調なので、この辺で。

お礼

時間の問題もあり、結局 Application.Quit で逃げました。
いろいろご回答をありがとうございました。

補足

こんにちは、ありがとうございます。

すみません、回答していないことがありました。（今さらですが）
エラーが出ているのはUserformのCommandButtonです。
発生しているエラーはSheet1の例と同じものです。

あと、もう一度確認ですが、Sheet1の例で、初めのエラー発生と回避策１のエラー回避から
UserformがModalだと（Sheetのイベントを抜けられなくて）エラーとなり、
Modelessだと（Sheetのイベントを抜けられるので）エラーとならない。
よって、UserformをModelessとするのが（この場合のSheetイベントを抜ける）回避策である、
と言えないでしょうか？
Closeでエラーとなるのは、SheetイベントだけでなくUserform等の他のイベントも含めた
閉じるための条件があり、ModelessにすればすべてOKではない、ということはわかります。
おそらく、現在の状況（Modelessにしてもエラーが起こる）は、そのためでしょう。

当方のシステムは、UserformをModelessにしても問題がないので、回避先１のほうを色々試し、
実は申し訳ありませんが、回避策２の方はあまり見てませんでした。
Application.OnTime Now(), "ProcF"　（・・・これ、知りませんでした。）
を使って色々やってみましたが、同じように2007,2003でエラーとなりました。

当方のシステム、結構大きいので、書き出すと限がなくなりそうなので、今まで書きませんでしたが、
ポイントを絞ってもう少し説明を加えます。

システムの概要は、Excelブック上で行う業務処理で、ユーザの操作は全てUserformで行ってます。
各シートで右クリックすると操作メニューが表示され、そこからメニューを選択して業務操作を行い
ます。操作メニューはSheetモジュールから、○○○.Show vbModeless で起動します。
操作メニューに、共通のボタンを用意し、そのボタンをクリックすると「表移動」というUserformを
次の記述で起動します。

unload （操作メニューのUserform）
表移動.Show vbModeless

この表移動というのは、Excelのシートを移動するためのもので、シートに対応するボタンがあり、
それをクリックすると、そのシートを表示します。
移動したシートでも、右クリックで操作メニューを起動しその共通ボタンでまたこの表移動を起動し
他のシートに移動できる、という具合に、何回か繰り返し様々なシートを参照する場合があります。

この表移動のUserformに[システム終了]のボタン（これがCommandButton2）があり、
これをクリックすると、ブッケを保存し、1つのブックならExcelを終了、複数ブックなら、現在の
ブックを終了としたいのです。・・・これが当初記載したプロシジャーで、ここでエラーが
起きます。

よろしければこの説明で、ご指摘いただく点がありましたら、ご提示ください。
最悪は、2010ではエラーとならないので、またブックが1つの場合の、Application.Quitでは
エラーとならないので、ブックが複数でExcelバージョンが2010未満の場合のみ、ユーザにブックを
1つにしていただくメッセージを出すことにより、対応しようと考えてます。
（不本意ですが…。）

回答No.2

cj_mover

こんにちは。お邪魔します。

CommandButtonといっても
UserformのCommandButtonなのか
ワークシート上に配置したActiveXコントロール（ツールバーコントロール）なのか
によっても変わって来ますが、
ひとまず今回は、可能性高そうなUserformに限って説明してみます。

基本的には、ご提示のプロシージャの記述の問題ではなくて、
CommandButtonやUserformの扱いの問題です。

とりあえず、ここを閲覧する人や回答を用意する人にも解って貰えるように
再現できるシステムを提示してみますね。
質問者さんも、障害が起きる原因を切り分けて把握する為に、
以下のテストを試して貰えるといいと思います。

準備
　新規のブックで
　UserformとUserform上にCommandButtonを追加（Userform1、CommandButton1）
　Sheet1モジュールに以下のプロシージャを設定
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
　　Cancel = True
　　UserForm1.Show
End Sub
　Userformモジュールに以下のプロシージャを設定
Private Sub CommandButton1_Click()
　　ThisWorkbook.Close SaveChanges:=True
End Sub
　ひとまず適当に名前を付けて保存。

テスト
　Sheet1の適当なセルを右クリックして
　Userform1を表示
　CommandButton1をクリック

結果
　→　エラーが再現される筈です。

エラーの原因
　　UserForm1.Show
　これ↑Userform閉じるまで次の行に進みません。
　つまりSheet1のイベントを実行中で、ブックを閉じることが出来ないのに
　ブックを閉じようとすると、エラーになります。

回避策１
　上記のSheet1モジュールのプロシージャを以下に変更
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
　　Cancel = True
　　UserForm1.Show vbModeless
End Sub
　これなら、Userformを閉じなくても、Sheet1のイベントを抜けてくれるので、
　エラーになりません。
　　UserForm1.Show
　は
　　UserForm1.Show vbModal
　の省略表記で、Userformを排他的に表示します。
　この状態では例えば、手作業でセルを選択したりできなくなります。
　対して
　　UserForm1.Show vbModeless
　は、UserformとExcelを並列に扱えるようにUserformを表示します・
　この状態では例えば、手作業でのセルの編集が可能です。
参考）一応、ShowメソッドをVBAのヘルプで確認してみてください。

さて、Userformをやっぱり排他モード（Modal）で表示したい場合は、
少し工夫が必要です。

回避策２　　Modal版
　上記のSheet1モジュールのプロシージャを以下に変更
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
　　Cancel = True
　　Application.OnTime Now(), "ProcF"
End Sub
　標準モジュールに以下のプロシージャを追加
Sub ProcF()
　　UserForm1.Show　　' UserForm1.Show vbModal
End Sub
　このような形でSheet1のイベントを抜けてからUserForm1をModal表示するようにすれば
　エラーは回避できます。

以上は、シートイベントからUserFormを呼び出す場合の例ですが、
どのような呼び出し方でも基本は一緒です。
簡単にいうと、
抜けるべきプロシージャを抜けないとブックを閉じられない、って感じでしょうか？

うまく行かないようでしたら、も少し具体的な説明を（コントロールに種類とか）
添えて補足欄にでも書いてみてください。

とりあえず、以上です。

お礼

ありがとうございます。
大変重要な指摘を頂いたと思います。Sheet1の例はそのとおりでした。
ただ残念ながらまだ解決していません。Excelのバージョンにより違うようです。
少し時間をいただいて、もうすこしまとめて、詳しくまた記載します。
すみませんが、また、よろしくお願いします。

補足

もう少し詳しく現象を記載します。
お手数をおかけしますがよろしくお願いします。

sheet1の例で言えることは、ModalのユーザフォームでCloseするとエラーになる、ということかなと
思いました。

そこで、質問したプロシジャーのユーザフォームをすべてModelessとなるようにしました。
しかし、Excel2007では複数ブックがある場合は、やはり同じようにNG（エラー）でした。
2003は未着手。
Excel2010ではOKです。逆にExcel2010ではModalのユーザフォームでもOKでした。（？？？）
Sheet1の例ではExcel2010でもNGでしたが・・・。

UserForm1.Show　とするとModalに、UserForm1.Show vbModeless　とするとModelessの
状態になりますよね？
また、ModelessのUserformからModalのUserformに行き、再びModelessとなるように連続する
場合、最後に設定したモードになっていると考えてますが、いいですか？
（このように連続すると2003や2007ではモードのエラーになったかもしれませんが、2010では
OKだと思います。）

もしかしたらこのあたりの私の理解が違っているかもしれません。

すみませんが、またご回答をよろしくお願いします。

、

回答No.1

DreamyCat

2003版と2013版で調べてみましたが
エラーにはなりません。
　PCシステムに問題があるものと思われます。

お礼

ありがとうございます。
ただ自宅の2台のパソコン（ExcelVer2007と2010）とネットカフェのパソコン（Excel2003）とで
実行してみて、自宅のExcel2007とネットカフェのExcel2003で発生しているので、
PCシステムというより、ExcelまたはVBAの問題のように思います。
そういえば、ファイルに何も変更がない場合はエラーにならない時もあり、変更があると毎回起きるようです。（すみません、最初から書くべきでした。）