セルの入力の履歴を記録する

はじめに

　ExcelではGoogleスプレッドシートのような入力の履歴を記録する機能は付いていませんが、Worksheet Change イベントを使って入力値の記録用のシートに履歴を残す事が可能です。
　まずは単一シートの記録を log という名前のシートに記録していくプログラムを書いてみます。
　
　ただし、今回の処理はセルの変化を逐次監視しているため動作が重くなる事がありますので注意してください。

単一シートの入力履歴を記録

　ここではSheet1の入力履歴を log シートに記録していきます。
　以下のプログラムは、Sheet1のモジュールに記述します。

　VBEでSheet1のシートモジュールを開いて、コードウインドウの左上を「Worksheet」、右上を「Cahnge」として、Worksheet Change イベントに以下のプログラムを書いていきます。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    
    With Worksheets("log")
    
    LastRow = .Cells(Rows.Count, "A").End(xlUp).row
    
    .Cells(LastRow + 1, "A") = Now()    '日時をセット
    .Cells(LastRow + 1, "B") = Target.Address   'セルのアドレスをセット
    .Cells(LastRow + 1, "C") = Target.Text      'セルの（文字列にした）値をセット
    .Cells(LastRow + 1, "D") = Application.UserName 'ユーザー名をセット
    
    End With
End Sub

プログラム解説

　Worksheet Change イベントプロシージャは、シート上のセルが変更された時に呼び出されるプロシージャです。
　引数の Target は Range型なので、セルのアドレスや値を取り出す事が出来ます。
　まずは記録用の log シートのA列最終行を取得して、最終行の直下に記録していきます。
　
　記録する値は、日時、セルのアドレス、セルの値、ユーザー名です。
　セルの値で文字列として取り出しているのは日付型などで変換された場合を想定しています。
　実際には以下の様に表示されます。

　ユーザー名は、ブックを他のPCのExcelで編集した場合に変化します。

履歴記録の削除

　記録はどんどん蓄積されていきますので、ある段階で古い記録を削除するようにしてみます。
　プログラムは、 log シートの最終行から上方向に見ていき、日付が本日以前の行を削除していきます。
　
　このプログラムは標準モジュールに書いて、ThisWorkbookのOpenイベントで実行するようにしてみます。

'古いデータを削除　/// 標準モジュールに書きます ///
Sub clearLogData()
    Dim i As Long
    Dim LastRow As Long
    Dim d As Date
    
    With Worksheets("log")
    
    LastRow = .Cells(Rows.Count, "A").End(xlUp).row
    d = Date '今日の日付を取得
    
    For i = LastRow To 2 Step -1  '最終行から先頭へループ
        If d > DateValue(.Cells(i, "A")) Then   '本日以前のデータ行を削除
            .Cells(i, "B").EntireRow.Delete
        End If
    Next i
    
    End With
End Sub

ThisWorkbookモジュールのOpenイベントプロシージャで上記のプログラムを実行するようにします。

　こうする事で、翌日ブックを開いた時に、前日までのデータは削除されます。
　何日が残したい場合はIf文を変更すればよいでしょう。

全シートの入力履歴を記録

　全てのシートの記録を残したい場合は、ThisWorkbookのSheetSelectionChangeを使えば良いでしょう。
　このイベントプロシージャは、全てのシートのセルの変更を監視します。
　ただし、今回のプログラムでは記録を残す log シートだけは除外した方が良いでしょう。

　SheetSelectionChange イベントプロシージャは、全シートを対象にして、どこかのセルが変更された時に呼び出されるプロシージャです。
　引数の Sh にはシートの情報。Target は Range型で、変更されたセルの情報を取り出す事が出来ます。

Private Sub Workbook_Open()
    Call clearLogData
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim LastRow As Long
    
    If Sh.Name = "log" Then Exit Sub    '記録用のlogシートの場合は処理を抜ける
    
    With Worksheets("log")
    
    LastRow = .Cells(Rows.Count, "A").End(xlUp).row
    
    .Cells(LastRow + 1, "A") = Now()    '日時をセット
    .Cells(LastRow + 1, "B") = Sh.Name   'シート名をセット
    .Cells(LastRow + 1, "C") = Target.Address   'セルのアドレスをセット
    .Cells(LastRow + 1, "D") = Target.Text      'セルの（文字列にした）値をセット
    .Cells(LastRow + 1, "E") = Application.UserName 'ユーザー名をセット
    
    End With
End Sub

尚、前回までの標準モジュールの clearLogData関数と、Workbook_Open についてはそのまま使います。
Sheet1のWorksheet Change イベントプロシージャについては削除しておいてください。
（二重に記録されてしまいます。）

今回のプログラムでは、log シートのB列にシート名が追加で記録されています。

まとめ

　今回はExcelVBAでセルの入力履歴を記録する方法を紹介しました。入力履歴と言ってもセルの値を記録しているだけなのでセルの書式設定などは記録できません。
　また一般的に、Worksheet Change イベントなどは処理が重くなる事が多いので用途によっては不向きかもしれません。速度を上げたい場合は、記録対象のRange範囲を限定するなどして対応してみてください。

　場合によっては、記録する logシートを非表示するなどしてユーザーがわからないように監視する方法も考えられます。参考にしてみてください。