[[20100807163344]]　『Changeイベント』（riy）　ページの最後に飛ぶ

『Changeイベント』（riy）
[[20100729234729]]を参考にさせてもらってコードを書きました。

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("m2:m231")) Is Nothing Then Exit Sub

     If Target.Formula = "" Then
        Application.EnableEvents = False
           Target.FormulaR1C1 = "=IF(RC[-9]="""","""",VLOOKUP(RC[-9],データ!C[-12]:C[-11],2,0))"
        Application.EnableEvents = True
     End If

End Sub

 ｍ２～ｍ231のどれかのセルのデータが消去されたら、
 そのセルに関数を書き込むというものですが ちっとも動きません。無反応です。
 校正をして欲しいです。
 宜しくお願い致します。

こんにちは

ｍ２～ｍ231のどれかのセルを右クリックして「数値と値のクリア」をしてもダメですか？

ダメなら、イミディエイトウィンドウに「Application.EnableEvents = True」をコピペして Enter してから試すとか。

（ウッシ）

ウッシさんへ

 有難うございます。
 この質問を書き込んでから、ファイルを一回閉じ、再度開きましたら
 コードが動くようになっていました。
 全く同じコードで触っていないのに、不思議不思議
 何が起こったのか？
 解決はしましたけど、気持ち悪いです。
 ウッシさんにはお手数掛けまして申し訳ありませんでした。
(riy)

 解決したようですが、ちょっと気になったので・・・。

 >Private Sub Worksheet_Change(ByVal Target As Range) 
 >   If Intersect(Target, Range("m2:m231")) Is Nothing Then Exit Sub
 >    If Target.Formula = "" Then
 >       Application.EnableEvents = False
 >          Target.FormulaR1C1 = "=IF(RC[-9]="""","""",VLOOKUP(RC[-9],データ!C[-12]:C[-11],2,0))"
 >       Application.EnableEvents = True
 >    End If
 >End Sub

 このコードだとセルm2～m3を選択し、この状態でDeleteキーを押すとエラーになりますよ
 (型が一致しない　というエラーです）
 どのように修正すればよいかは、仕様によります。
 検討してみてください。

 あれっ、回答投稿されてるウッシさん　って、以前、VBA質問箱でも回答されていた方ですか？

 だとしたら、お久しぶりですね！！

 ichinose

ちょっと、お借りしますね。

 ichinose さん、こんばんは
VBA質問箱ではそれほど回答した記憶は無いんですけど・・・
ExcelVBA あまり使わないとどんどん忘れてしまうし、Excel の 2007 バージョンも使えないままなのに、2010 まで出てしまって。
このままではマズいので少し回答しながら ichinose さん始め他の方の回答で勉強しようかなって思ってます。
（ウッシ）

 ichinoseさんへ
 有難うございます。
 >このコードだとセルm2～m3を選択し、この状態でDeleteキーを押すとエラーになりますよ
 >(型が一致しない　というエラーです）
 >どのように修正すればよいかは、仕様によります。
 昨日　テストしていて、おっしゃるような場面で「型が一致しません」とエラーになったので

 If Target.Count > 1 Then Exit Sub
 を付け加えました。そういう事での指摘と解釈して宜しいですか？
 (riy)

 >If Target.Count > 1 Then Exit Sub
 >を付け加えました。そういう事での指摘と解釈して宜しいですか？
 これは、riyさんが決めた仕様によります。

 >Private Sub Worksheet_Change(ByVal Target As Range) 
     If Target.Count > 1 Then Exit Sub
 >   If Intersect(Target, Range("m2:m231")) Is Nothing Then Exit Sub
 >    If Target.Formula = "" Then
 >       Application.EnableEvents = False
 >          Target.FormulaR1C1 = "=IF(RC[-9]="""","""",VLOOKUP(RC[-9],データ!C[-12]:C[-11],2,0))"
 >       Application.EnableEvents = True
 >    End If
 >End Sub

 このようにしたということですよね？

 確かにこのようにすれば、エラーにはなりませんね！！
 そもそもの仕様は、
 「セル範囲m2:m231内でセルの変更があった場合、
 そのセル=""　であったなら、数式を埋め込む」ということですね！！

 >If Target.Count > 1 Then Exit Sub

 これを付加することでエラーはなくなりますが・・・、
 セル範囲m2:m3を選択した状態でDeleteキーを押すと
 m2:m3は、クリアされた状態で処理が完了してしまいます。
 セル範囲m2:m231がこのような状態でもかまわないなら　これでもよいですよね！！

 そままのクリアされた状態では困るなあ　せめて入力前の状態に戻して
 エラーメッセージでもださなきゃ　というなら

 Option Explicit
 Private Sub Worksheet_Change(ByVal Target As Range)
     Application.EnableEvents = False
     If Target.Count > 1 Then
        Application.Undo
        MsgBox "単一セルの変更だけ可能です"
     Else
        If Not Application.Intersect(Target, Range("m2:m231")) Is Nothing Then
           If Target.Formula = "" Then
              Target.FormulaR1C1 = "=IF(RC[-9]="""","""",VLOOKUP(RC[-9],データ!C[-12]:C[-11],2,0))"
           End If
        End If
     End If
     Application.EnableEvents = True
 End Sub

 こんなコードが考えられます。

 いやいや、これだとExcelの複数セルへの同時入力という便利な機能が制限されてしまう
 複数セル範囲を選択してDeleteキーを押してもセル範囲m2:m231の範囲には、
 決められた数式を配置させたい　ということなら・・・・、

 Option Explicit
 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim rng As Range
     Dim crng As Range
     Set rng = Application.Intersect(Target, Range("m2:m231"))
     If Not rng Is Nothing Then
        Application.EnableEvents = False
        For Each crng In rng
           If crng.Formula = "" Then
              crng.FormulaR1C1 = "=IF(RC[-9]="""","""",VLOOKUP(RC[-9],データ!C[-12]:C[-11],2,0))"

           End If
        Next
        Application.EnableEvents = True
     End If
 End Sub

 こんなコードが考えられます。

 私は、もう何年もシートイベントを使ったコードは書いていませんが、
 これを使っていた頃は、殆ど最後に提示したような仕様にしていました。

 検討してみてください。

 ichinose

ichinoseさんへ

 感動しました。
 昨日テストをしながら、なんとなく感じていた不満がこれでした。
 不満解消を具体的に模索するには至らずに、でも何となくもやもや状態でした。
 これです！
 すばらしいコード有難うございます。
　(riy)

 すみません。
 もうひとつ質問させて下さい。
Private Sub Worksheet_Change(ByVal Target As Range)
     Dim rng As Range
     Dim crng As Range
     Set rng = Application.Intersect(Target, Range())
     If Not rng Is Nothing Then
        Application.EnableEvents = False
        For Each crng In rng
           If crng.Formula = "" Then
              crng.FormulaR1C1 = "=IF(RC[-9]="""","""",VLOOKUP(RC[-9],データ!C[-12]:C[-11],2,0))"

           End If
        Next
        Application.EnableEvents = True
     End If
 End Sub
　
 m10の値を手動で定数に変更してあります。
 m2～m3も手動で定数に変更してあります。
 m2～m3を選択して値をデリートするとm2～m3に数式が埋め込まれます。
 m10は変化しません。

 m10は変化してほしくないので、このコードで問題ないのですが、
 m10の値は定数で数式ではないので、m10に数式が埋め込まれない理由がわかりません。
 For Each crng In rng
           If crng.Formula = "" Then
↑だとm10も変化しそうに一見思えるのですが？

 理解して使いたいので、ご教示願えないでしょうか
 宜しくお願いいたします。
　(riy)

 >m2～m3を選択して値をデリートするとm2～m3に数式が埋め込まれます。
 >m10は変化しません。

 Set rng = Application.Intersect(Target, Range("m2:m231"))

 このコードでrngは、どのセル範囲を取得している　と思いますか？
 m2とm3を選択して　デリートしているのですから、Targetは、m2:m3を取得します。
 更に　Application.Intersect(Target, Range("m2:m231"))では、
 target(m2:m3)とRange("m2:m231")の共有セル範囲を返します。つまり、
 rngは、m2:m3　というセル範囲を取得しているわけです。

 よって、
　For Each crng In rng
           If crng.Formula = "" Then

 セル　m2とm3　に対してループ処理をしているので　m10はループ対象外なんです。

 >m10の値は定数で数式ではないので、m10に数式が埋め込まれない理由がわかりません。
 数式が埋め込まれていなければ、Formulaは、""ではありませんよ。Value値と等しいはずですよ
 つまり、定数が入力されていれば,その定数値を返します。

 ということで、私が投稿を拝見した限り、二つの誤解があるかと思います。
 再度、調べてみてください。

 ichinose

ichinoseさんへ

 ばっかな私でも今度こそ理解できました。
 丁寧な解説有難うございます。
 感謝感謝です。
 (riy)

コメント返信：

[ 一覧(最新更新順) ]

[[20100807163344]] 『Changeイベント』（riy） ページの最後に飛ぶ

[[20100807163344]]　『Changeイベント』（riy）　ページの最後に飛ぶ