エクセル2007 Sub Auto_Close()について教えて下さい

質問

ドラッグとドロップを禁止するために以下のマクロを入れています。

Sub Auto_Open()
'無効
Application.CellDragAndDrop = False
End Sub

Sub Auto_Close()
'有効
Application.CellDragAndDrop = True
End Sub

上記のマクロだと、ファイルを開いた時は CellDragAndDrop は無効なのですが
終了するときに、[終了]-[キャンセル]して再度作業を進めようとすると CellDragAndDrop が有効になってしまいます。

Sub Auto_Close()　で、[終了]-[キャンセル]の場合のみ、
　Application.CellDragAndDrop = False　とするには、どのうように記述すれば良いのでしょうか？

よろしくお願いします。

回答No.1

myRange · ベストアンサー

なぜ質問のようになるかというと、
書き込み確認メッセージが出る前にAuto_Closeが処理されるからです。

で、代わりに、Workbook_BeforeCloseイベントで、
独自の書込み確認メッセージを表示してコントロールしてみたらどうでしょう。

確認メッセージにはデフォルトのメッセージと同様、
「はい」「いいえ」「キャンセル」の３つのボタンを表示させます。

また、Auto_OpenもWorkbook_Openに変えて統一しておくといいでしょう。

Atuo_OpenとAuto_Closeは削除して
以下のコードを、ThisWorkBookモジュールにコピペ。

'-------------------------------------------
Private Sub Workbook_Open()
　　Application.CellDragAndDrop = False
End Sub
'-------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　Dim msg
　　msg = MsgBox(ThisWorkbook.Name & " を保存しますか？", vbYesNoCancel, "確認")
　　Select Case msg
　　　　Case vbYes
　　　　　　　　Application.CellDragAndDrop = True
　　　　　　　　ThisWorkbook.Save
　　　　Case vbNo
　　　　　　　　Application.CellDragAndDrop = True
　　　　　　　　ThisWorkbook.Saved = True
　　　　Case Else
　　　　　　　　Cancel = True
　　　　End Select
End Sub
'-------------------------------------------------

Auto_CLoseではなぜ上記のことができないかはお分かりですね？
以上です。

お礼

ありがとうございます！
[終了]-[キャンセル]でもドラッグ・ドロップが無効のまま作業することができました。

ただ、「確認」のメッセージボックス[はい][いいえ]を選択すると、
「Microsoft Office Excel」の[はい][いいえ][キャンセル]メッセージが再度表示されてしまいます。

この表示が出ないようにすることはできるのでしょうか？

度々申し訳ありませが、ご教示いただけますでしょうか。
よろしくお願いします。

回答No.3

myRange

またまた、myRangeです。

xl２００７でも検証しました。
やはり、デフォルトの確認メッセージは出ません。

検証は、マクロを含んでいるブック(thisworkbook）ひとつで試しています。

当方のコードで、独自のメッセージとデフォルトのメッセージがでるとしたら
どんな条件のときなのか当方としても知っておく必要がありますので
先の補足要求および、質問者の検証条件など、提示してください。

以上です。

お礼

お返事・お礼が遅くなって申し訳ありません。

ご指摘のとおり、Auto_Closeに図形を貼り付けるマクロが残っていました。
Auto_Closeを完全に削除し　Workbook_BeforeCloseに以下のように書き込んで解決しました。

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim msg
msg = MsgBox(ThisWorkbook.Name & " を保存しますか？", vbYesNoCancel, "確認")
Select Case msg
Case vbYes
Application.CellDragAndDrop = True
Sheets("入力１").Shapes("A").Visible = True
Sheets("入力２").Shapes("B").Visible = True
Sheets("入力３").Shapes("C").Visible = True
ThisWorkbook.Save
Case vbNo
Application.CellDragAndDrop = True
Sheets("入力１").Shapes("A").Visible = True
Sheets("入力２").Shapes("B").Visible = True
Sheets("入力３").Shapes("C").Visible = True
ThisWorkbook.Saved = True
Case Else
Cancel = True
End Select
End Sub

お騒がせして本当に申し訳ありません。重ねてお礼とお詫びをいたします。
ありがとうございました。

回答No.2

myRange

回答１、myRangeです。

当方で試してみましたが、はい、いいえ、でもデフォルトのメッセージは表示されません。
ま、初めからそこら辺りは考慮したつもりですが。
但し、xl2007が手元にないのでxl2000での検証です。

で、確認。
（１）Auto_Closeは削除したか
（２）当方の回答の外のところに、SaveやCloseなどのコードはないか
（３）当方の回答部分をコピペしたはずですが、それを修正加筆してないか
　　　もしそうなら、その部分（Workbook_Closeイベント）をアップしてください

以上確認願います。