派遣事務員の迷走

派遣事務員コロ子。会社の犬。顔出しNG。常に迷走している。

Worksheet_ChangeイベントとWorksheet_SelectionChangeイベント(前回の種目の自動採番コード変更しました)

こんにちは。
派犬事務員のコロ子です。

前回の記事、種目別の自動採番のコードで
空腹おやじ(id:Z1000S)さんより、

この処理だと、登録済みのA列のセルで、B列の値が最大値ではない行でF2を押して編集状態にして、そのまま何も変えないでEnter押しちゃうと、B列の値が更新されちゃいますが、大丈夫ですか?(F2、Escだと大丈夫ですが)

とご指摘頂きました。
おおっつ!ヤバイ!大丈夫じゃない!
そこまで考えが至らなかった。

下記の図のように、もうすでに値が入っているA列を編集状態にする。

f:id:SNegishi:20190814105224p:plain

    ↓

値を変更しないで確定すると最大値+1を採番してしまう。
f:id:SNegishi:20190814105258p:plain

これは良くない!!重大なバグ!
実はコロ子の会社で作ったマクロは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)さんのアイディアをそのまま使わせて頂きました。
どうもありがとうございました!