EXCEL2010 VBA SelectionChangeイベントについて

EXCEL2003(.XLS)ファイルをEXCEL2010環境（互換モード）で実行しています。
3行目から行単位のデータがあり、１列目をダブルクリックすると、フォーム入力画面が開きます。
データが存在する３列目をクリックすると、Range("J1")を、その値で更新します。

このファイルを開いて、最初に1列目をダブルクリックしてフォームを開くと、後の処理は問題なく動くのですが、最初に3列目をクリックして、SelectionChangeイベントを発生させると、必ず画面がフリーズしてしまいます。
どうもダブルクリックイベント処理の中の Cancel=True 処理がヒントのような気がしますが、
それにしても、どう対応すべきかわかりません。
ご存じの方、よろしくお願いいたします。


以下は関連するコードです。
（シート内のモジュール）
=====================================================
' ダブルクリックイベント処理
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 And _
Target.Row >= 3 Then
If Target.Value <> "" Then
'カード入力画面
Call S_Card_IN(Target.Row)
Cancel = True 'セルの選択状態を解除
End If
End If
End Sub

' 処理中NO 更新
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row >= 3 And Target.Column = 3 Then
If Cells(Target.Row, 3) <> "" Then
Application.EnableEvents = False
ActiveSheet.Range("J1") = ActiveSheet.Cells(Target.Row, 3)
Application.EnableEvents = True
End If
End If
End Sub

質問者からの補足コメント

• 

  皆様、いろいろとアドバイスを有り難うございました。
  解決しましたので、ご報告いたします。
  
  原因は、フォーム画面上の複数のテキストボックスのControlSourceに同じEXCELのセルを設定していたのが直接の原因のようです。
  EXCELの任意の行の各セルとフォーム上のテキストエリアをリンクさせるために、UserForm_Initializeで、動的にこのControlSource を変更（これは正しく）していましたが、画面デザイン上で、セルが重複してしまった。これは、以前動いていたものに、カラムを追加したための修正漏れでしたが、
  コンパイルエラーにもならないしUserForm_Initializeで正しい値が設定できているので問題ないと考えますが、何故か、Worksheet_SelectionChangeでエラーの要因になるようです。納得は出来ませんが。
  予定通りの動きになりました。

    補足日時：2016/08/13 14:26

No.5 回答者： WindFaller 回答日時： 2016/08/13 10:39

先ほど、本当の意味でのクリックイベントを作ってみましたが、おすすめできるようなものではありませんでした。

ご存知かもしれませんが、API関数の「GetAsyncKeyState」を使う方法です。

実際、私の方では、考えられるものは全部お話しました。

>CANCEL=TRUE
Cancel =True というのは、ダブルクリックしたままだと、入力待ち状態になって、マクロが動かなるのを防ぐためです。しかし、それは、ファイル上のトラブルとは関係がありません。そういう状態が起これば別ですが。
つまり、フリーズ状態というものも、タスクマネージャで、リセットをしなければならないのか、ESCやVBEditor 上の「　|>, ||, ■　」ボタンの■で止められるものもあります。

ところで、同じ上記になった時に、私ならどうするか、デバックの手順だけお書きしておきます。

まず、問題になっているのは、SelctionChange側だと仮に特定化します。
(仮にそうでなくても）

今回は、特別に
　'Application.EnableEvents = False
　'Application.EnableEvents = True
　は、コメントブロック「'」を入れておいてもよいと思います。
　途中で止めることになるかもしれませんから。

1.
● Sub Worksheet_SelectionChange(ByVal Target As Range)
VBEditor の左端をクリックすると●が入ります。
ワークシートで入力すると、ここで止まりますから、F8を押して進めていきます。

以下の行まで来ていて、それで終わってしまうでしょうか。
ActiveSheet.Range("J1") = ActiveSheet.Cells(Target.Row, 3)

2.
もし、ここでF8を押すとフリーズするようなら、
3列目のどこかをクリックした値のはずですが、
ActiveSheet.Cells(Target.Row, 3)
ただしく、値がきているか調べます。

a =ActiveSheet.Cells(Target.Row, 3)
で、ローカルウィンドウを見ます。

ActiveSheet.Range("M1") = ActiveSheet.Cells(Target.Row, 3)

---------------------------
If Target.Value <> "" Then
　On Error Resume Next
　　 Range("M1").Value = Target.Value
　On Error GoTo 0
End If
----------------------------
次に、
ActiveSheet.Range("J1").Value
J1のセルがどうなっているか、書き込める状態か、
別のマクロで試験してみるとよいでしょう。

ActiveSheet.Range("J1").Value =123

とか。

さて、本来、ここまでで終わるはずです。

3.しかし、
>EXCEL2003(.XLS)ファイルをEXCEL2010環境（互換モード）で実行しています。

拡張子が、"xls"のバイナリ状態ですから、Excel2010環境と旧環境とを分けるために、Excel 2010 では、xlsm にしてみて、様子を図るのがよいのではないかと思います。下位バージョンで作ったものを上バージョンに上げて作り直し、直したものは、上位バージョン専用にするわけです。

ワークシートに問題ある可能性がないともかぎりません。

この回答へのお礼

「WindFaller」いろいろ有り難うございます。
現時点での結果報告をいたします。
＞以下の行まで来ていて、それで終わってしまうでしょうか。
＞ActiveSheet.Range("J1") = ActiveSheet.Cells(Target.Row, 3)
ここで、EXCELが応答しなくなり「X」で、エラー回復のVersion1ファイルが作成されます。

＞ActiveSheet.Cells(Target.Row, 3)
＞ただしく、値がきているか調べます。
値は正しく取得できます。

＞ActiveSheet.Range("M1") = ActiveSheet.Cells(Target.Row, 3)
結果は同じ

＞ 拡張子が、"xls"のバイナリ状態ですから、Excel2010環境と旧環境とを分けるために、Excel 2010 では、xlsm に＞してみて、様子を図るのがよいのではないかと思います。
結果は同じ

＞ワークシートに問題ある可能性がないともかぎりません。
試しに、モジュールなどを順次解放していったところ、ダブルクリックで表示しているFormモジュールを
解放したところ、Worksheet_SelectionChangeイベントは、問題なく動くようになりました。
しかしフォームモジュールのどこを見てもエラーの原因はわかりません。
そこで、フォ－ムを最初から作り直しています。項目数が多いので、確認がすぐ出来ませんが、
また結果をお知らせいたします。

お礼日時：2016/08/13 12:00

No.4 回答者： WindFaller 回答日時： 2016/08/12 20:07

#1の回答者です。

読みが外れてしまいましたね。
だから、DoEvents が効きません。

>現在はEXCEL2010の環境ですが、
Excel 2010 は、Event Driven 型では、無限ループに入っても途中で離脱できるようになっていますから、逆だと思いました。

>クリックイベントのみにし、フォーム画面の表示もボタンをもう一つ作成すれば可能なのでしょう。

本当に、クリックイベントがあればですが、それはVBAの範囲内ではありません。
私は、実務的には、SelectionChange は不安定なので、なるべく他のものを使うようにしています。もし、絶対的に両方を使うつもりなら、両方をClass にしてしまって、片方のイベントが動いている時には、もう片方は効かないようにしてしまうこともありなのかもしれません。とはいえ、それは、#2さんが書かれている内容と同じ意味です。

>ActiveSheet.Range("J1") = ActiveSheet.Cells(Target.Row, 3)
それと、使うのなら、Me キーワードを使うべきだと思いますが、
　Me.Range("J1").Value = Target.Value

ただ、それで、フリーズ状態になるとは思えません。ワークシート上のどこかのエラーが発生している場合は、この限りではありません。

SelectionChangeは、もともと、セルの移動した時に発生するイベントです。

そこで、そのイベントは温存したままに、わたし流に書いてみますから、試してもらえませんでしょうか？

このマクロで何を意味しているかは、想像つきますでしょう。
それに、現在の内容では、本来、Application.EnableEvents　を入れる必要性はないはずです。他にも、インターラプト

しかし、万が一、他のイベントが働くことを考えると、以下のようにせざるを得ません。これでどうでしょうか。根本的な原因は未だつかめていない状態ですから、なんとも心もとないのですが。

'//

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　If Target.Column <> 3 Then Exit Sub
　If Target.Row < 3 Then Exit Sub
　If IsError(Target) Then Exit Sub
　Application.EnableEvents = False
　　If Target.Value <> "" Then
　　　On Error Resume Next
　　　　Range("J1").Value = Target.Value
　　　On Error GoTo 0
　　End If
　Application.EnableEvents = True
End Sub


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
If Target.Row < 3 Then Exit Sub
Call S_Card_In(Target.Row)
Cancel = True
End Sub

この回答へのお礼

有り難うございます。
「WindFaller」さんのコードをそのまま試しましたが、結果は全く同じです。

因みに
>本当に、クリックイベントがあればですが、それはVBAの範囲内ではありません。
すみません安易に書いてしまいました。SelectionChangeイベントだけで対応するならです。

何度も書きますが、ファイルを開いて、最初に1列目をダブルクリックしてフォームを開くと、後の処理は問題なく動くのです。
理解できませんが、CANCEL=TRUEの処理に意味があるのでしょうか？

お礼日時：2016/08/12 21:26

No.3 回答者： siffon9 回答日時： 2016/08/12 18:05

No.2です。

flagなんか用意しなくても、TargetがJ1セルだったらEnd Subしてしまえば良いのかもしれません。

No.2 回答者： siffon9 回答日時： 2016/08/12 17:54

> ActiveSheet.Range("J1") = ActiveSheet.Cells(Target.Row, 3)

> でフリーズするようです。

Application.EnableEvents = Falseが利かずに無限にSelectionChangeイベントが発生している印象があります。
以前、Excel2013の環境で、同様の事象が発生したことがあります。
その時はフラグとなる変数を作成して、無限イベントを回避しました。

Private flag As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　If flag Then
　　　　Exit Sub
　　End If
　　flag = True

　　本来の処理

　　flag = False
End Sub

あまり褒められた方法とも思えませんが、一応ご参考まで。

No.1 回答者： WindFaller 回答日時： 2016/08/12 10:17

こんにちは。

フリーズするのは、Excel2003のはずです。
私は、今下位バージョンでは試してはみませんので、フリーズは起きていません。しかし、ここに出されたコードを見る限りは、理屈ではフリーズする要素はありませんが、いかにも失敗しそうなコードであることは分かります。
(もしデバッグするなら、SelectionChange側に、DoEventsをどこかに入れてあげれば、Escキーで、とりあえず止まるはずです。）

SelectionChange と DoubleClickイベントを明確に分けるものなどは、今のコードではありません。
もし、現状のコードのままですと、もう一つ、何か、ふたつを分ける「離脱」条件が必要だと思います。

もともとの設計の問題なのですが、SelectionChangeは、片方でイベントを使ってしまったから、付け足したのではありませんか？実務的に、SelectionChangeを使う場所は、それほど多くありません。

例えば、このように統合することはできないのでしょうか。
最初、感覚的に違いは感ずるとは思いますが、確実性は上がると思います。

' 一つの提案として
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
　Cancel = True
　If Target.Row >= 3 Then
　　If Target.Column = 1 Then
　　　If Target.Value <> "" Then
　　　　'カード入力画面
　　　　Call S_Card_In(Target.Row)　　　　
　　　End If
　　ElseIf Target.Column = 3 Then
　　　If Cells(Target.Row, 3).Value <> "" Then
　　　　'Application.EnableEvents = False 'このコードでは不要
　　　　Me.Range("J1").Value = Target.Value
　　　　'Application.EnableEvents = True　''このコードでは不要
　　　End If
　　End If
　End If
End Sub

'Me キーワードを入れる理由はありませんが、ActiveSheet としていましたので、そのかわりに入れました。

この回答へのお礼

ご助言ありがとうございます。
DoEventsを入れてみましたが、ESCで制御は戻りませんでした。
MSGBOX等で、どこまで動いているかチェックしましたが、どうも
ActiveSheet.Range("J1") = ActiveSheet.Cells(Target.Row, 3)
でフリーズするようです。

質問内容は全体の仕様まで説明できないので部分的に記述していますが、
selectionChangeによって、どの行のデータを使用するか指示し、その後、ボタン操作によってワードへ
内容を転記する用にしています。

BeforeDoubleClickは、EXCELの特定の行を編集にあたり列が多すぎて操作しにくいのでフォーム画面に
展開し、修正できるようにしています。
つまり、クリックはデータを選択で、ダブルクリックは、フォーム画面でデータ編集にしたいのです。

このプログラムは、元々EXCEL2003の時に作成して、この制御は問題なく何年も動いていました。
現在はEXCEL2010の環境ですが、ちょっと仕様が変わって旧プログラムに機能を追加しただけで
問題の部分は変更を加えておりません。

ですから、ご提案いただいた方法をヒントにするなら、逆にクリックイベントのみにし、フォーム画面の表示も
ボタンをもう一つ作成すれば可能なのでしょう。

それにしても、ファイルを開いて、最初に1列目をダブルクリックしてフォームを開くと、後の処理は問題なく動くのが理解できません。

お礼日時：2016/08/12 11:32