Worksheet_ChangeイベントとWorksheet_SelectionChangeイベント(前回の種目の自動採番コード変更しました)
こんにちは。
派犬事務員のコロ子です。
前回の記事、種目別の自動採番のコードで
空腹おやじ(id:Z1000S)さんより、
この処理だと、登録済みのA列のセルで、B列の値が最大値ではない行でF2を押して編集状態にして、そのまま何も変えないでEnter押しちゃうと、B列の値が更新されちゃいますが、大丈夫ですか?(F2、Escだと大丈夫ですが)
とご指摘頂きました。
おおっつ!ヤバイ!大丈夫じゃない!
そこまで考えが至らなかった。
下記の図のように、もうすでに値が入っているA列を編集状態にする。
↓
値を変更しないで確定すると最大値+1を採番してしまう。
これは良くない!!重大なバグ!
実はコロ子の会社で作ったマクロはB列でダブルクリックすると採番するよういしていて、ブログ用にちょっとアレンジしたら、この有様。
(会社で問題なく使えてるから安心して!)
更新したくないのであれば、SelectionChangeイベントで、変更前の値を保持しておいて、Changeイベントで変更されているか確認する等、なんらかの処理が必要になるかも。by空腹おやじ(id:Z1000S)さん
とアドバイスいただいたので、早速作り直さなければ!
前回書いたダメなコード(コピー不可)
Private Sub Worksheet_Change(ByVal Target As Range) Dim buf As Range Application.ScreenUpdating = False '1列目(A列)に変更があった時 If Target.Column = 1 Then '初期化 Range("B" & Target.Row).Value = "" 'A列でフィルタ Range("A1").AutoFilter Field:=1, Criteria1:=Range("A" & Target.Row).Value 'アクティブセル領域の可視範囲を取得 Set buf = Range("A1").CurrentRegion.Columns(2).SpecialCells(xlCellTypeVisible) '最大値+1を取得 Range("B" & Target.Row).Value = Application.WorksheetFunction.Max(buf) + 1 'フィルター条件解除 ActiveSheet.ShowAllData End If Application.ScreenUpdating = True End Sub
*前回記事のコードは修正しました。
何がダメかというと、 Worksheet_Changeイベントについて分かっていない。
Worksheet_Changeイベント
イベントの名前が「Change」なのでセルに値や数式を入力したり、入力されているデータを変更した時に発生するイベントだと思っていた。
Changeイベントは、名前に騙されやすいので要注意です。
by空腹おやじ(id:Z1000S)さん
ところが入力されているデータを変更しなくても「編集状態」にして「確定」すると変更したとみなされ、イベントが発生してしまう。
知らなかった~。これ危険だわー。
*「編集状態」にしてもescキーでキャンセルすればイベントは発生しない。
また、セルに色を付けたり、書式などの変更でもイベントは発生しない。
Worksheet_SelectionChangeイベント
イベントの名前の通り、選択セルが変更されたらイベントが発生する。
引数Targetに変更後のセル(カーソルがあるセル)のRangeオブジェクトが取得できる。
考え方
ChangeイベントとSelectionChangeイベントのダブル使い。
①A列のセルにカーソルが移動したら、Worksheet_SelectionChangeイベントでカーソルのあるセルの値と、の隣のB列の値を空いてるセルに仮置きする。
A列の値をAA列、B列の値をBB列に入力。
②そのセルでChangeイベントが発生したら、採番し隣のB列に値を入れる。
③もし、Changeイベント発生時のA列の値と、保存してあるAA列の値が同じ場合は、保存しているBB列の値をB列に入れる。
【コード】Worksheet_SelectionChangeイベント
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 And Target.Row >= 2 Then 'AA列、BB列に仮置き Range("AA:BB").Clear Range("BB" & Target.Row).Value = Range("B" & Target.Row).Value Range("AA" & Target.Row).Value = Range("A" & Target.Row).Value End If End Sub
【コード】Worksheet_Changeイベント
Private Sub Worksheet_Change(ByVal Target As Range) Dim buf As Range Application.ScreenUpdating = False Application.EnableEvents = False 'イベント禁止 If Target.Column = 1 Then 'オートフィルタ Range("A1").AutoFilter Field:=1, Criteria1:=Range("A" & Target.Row).Value 'アクティブセル領域の可視範囲を取得 Set buf = Range("A1").CurrentRegion.Columns(2).SpecialCells(xlCellTypeVisible) '最大値+1を取得 Range("B" & Target.Row).Value = Application.WorksheetFunction.Max(buf) + 1 'フィルター条件解除 ActiveSheet.ShowAllData 'A列に変更がない場合は元の値に戻す If Range("A" & Target.Row).Value = Range("AA" & Target.Row).Value Then Range("B" & Target.Row).Value = Range("BB" & Target.Row).Value End If 'AA列、BB列に仮置き Range("AA:BB").Clear Range("BB" & Target.Row).Value = Range("B" & Target.Row).Value Range("AA" & Target.Row).Value = Range("A" & Target.Row).Value End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub
「Worksheet_SelectionChangeイベントで変更前の値を保持しておいく」のところを静的変数Staticを使おうとしたけど、モジュール変数として使う事がでなかった。
「プローシシャーの外では使えません」のエラーメッセージが出た。
結局上手くできず、いつもの仮置き方式。
仮置き方式でないなら、どのように作るものなのでしょうか・・・?
今回は空腹おやじ(id:Z1000S)さんのアイディアをそのまま使わせて頂きました。
どうもありがとうございました!