EXCEL VBA EXCEL操作のログ（記録）をブック単位に記録する（入力履歴・修正箇所記録・変更履歴）
EXCEL VBA EXCEL操作のログ（記録）をブック単位に記録する（入力履歴・修正箇所記録・変更履歴）
社内で不特定多数によるEXCELファイルを扱う場合、いつの間にかデータが変更されていたり、値が置き換わったりする事があると思いますが、この時に何時・誰が・何を変えたか分かるように、EXCEL（エクセル）のブックごとに、利用者と変更箇所を履歴管理ができると方が共有したファイルを管理しやすいと思います。特にマスターデータとして管理するEXCELファイルは、データの変更履歴が分かる方が管理しやすいと思いますので、このようにマスターデータや基本データなどを管理する時には、とても管理しやすいです。それでは、サンプルプログラムを交えて順番に説明いたします。
●【Worksheet.Change イベントについては、下記を参照して下さい】：参考（Microsoft社 様）
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheet.change
●【Worksheet.SelectionChange イベントについては、下記を参照して下さい】：参考（Microsoft社 様）
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheet.selectionchange
EXCEL VBA EXCEL操作のログ（記録）をブック単位に別シートへ記録する（変更日時・変更セル番号・変更内容・ユーザー名）
下記のサンプルプログラムは、指定のエクセルシートの操作ログを記録するサンプルプログラムです。記録するログについては、変更日時・変更セル番号・変更内容・アプリケーションのユーザー名です。追加・変更等の操作をシート「社員情報」で行った内容が別シート「LOG」に操作ログとして記録されます。
【プログラム処理手順】（ワークシート「人事情報」の内容が変更された場合に実行）
① 内容が変更されたセル番号を取得します。（列番号・行番号）
② ワークシート「Log」のA列の最終行を取得します。（ログの登録位置を取得するため）
③ ワークシート「Log」のA列に変更された日付・時間が登録されます（YYYY/MM/DD HH:MM:SS)
④ ワークシート「Log」のB列に変更されたセル番号が登録されます。（例：＄A$1)
⑤ ワークシート「Log」のC列に変更された内容（文字列）が登録されます。
⑥ アプリケーションソフト（EXCEL）のユーザー名がワークシート「Log」のD列にユーザー名が登録されます。
※ログインユーザー名では無い。（ログインユーザー記載方法は、下記を参照して下さい。）
【プログラムの実行条件】
●今回のプログラムについては、ワークシートのイベントプロシージャを利用してプログラムを実行します。プログラムは、Sheet1（社員情報）に登録します。
Private Sub Worksheet_Change(ByVal Target As Range)
・ワークシート名「人事情報」・・・・人事情報が登録されている。
・ワークシート名「Log」・・・・・・ワークシート「人事情報」で追加・変更・削除などを行った場合に、このシートに変更履歴（ログ）が登録されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
' ' Private Sub Worksheet_Change(ByVal Target As Range) Dim XCol, YRow, lRow As Long XCol = Target.Column '列番号を取得します。 YRow = Target.Row '行番号を取得します。 lRow = Worksheets("LOG").Range("A" & Rows.Count).End(xlUp).Row + 1 'ワークシート「LOG」A列の最終行を取得します。 Worksheets("LOG").Range("A" & lRow) = Format(Now(), "YYYY/MM/DD HH:MM:SS") '更新日時をワークシート「LOG」A列に日時を転記します。 Worksheets("LOG").Range("B" & lRow) = Target.Address(YRow, XCol) '変更位置（セル番号）をワークシート「LOG」B列にセル番号を転記します。 Worksheets("LOG").Range("C" & lRow) = Cells(YRow, XCol) '更新内容をワークシート「LOG」C列に転記します。 Worksheets("LOG").Range("D" & lRow) = Application.UserName 'アプリケーションのユーザー名をワークシート「LOG」D列に転記します。 End Sub ' |
●実行前～実行後 ※シート「人事情報」の内容を変更等を行うとシート「Log」に変更内容が記入されます。
（画面クリックして拡大）
EXCEL VBA EXCEL操作のログ（記録）をブック単位に別シートへ記録する（変更前・変更後を記録・コンピューター名・ユーザー名を記録）
下記のサンプルプログラムは、サンプルプログラム①の応用になります。記録するログについては、更新日時・変更セル番号・更新前・更新後・コンピューターユーザー名・コンピュータ名です。追加・変更等の操作をシート「社員情報」で行った内容が別シート「LOG」に操作ログとして記録されます。
【プログラム処理手順】（ワークシート「人事情報」の内容が変更された場合に実行）
① 変更前のセル内容を一時保管します。
② WshNetworkオブジェクトを利用してユーザー名・コンピューター名を取得する準備を行います。
③ セルが変更された列番号・行番号を取得します。
④ ワークシート「Log」A列の最終行を取得します。※Logに追記するため最終行を取得
⑤ ワークシート「Log」のA列に現在の日時を転記します。
⑥ ワークシート「Log」のB列に変更位置（セル番号）を転記します。
⑦ ワークシート「Log」のC列にセルの変更前①の内容を転記します。
⑧ ワークシート「Log」のD列に変更後のセル内容を転記します。
⑨ ワークシート「Log」のE列に使用しているPCのユーザー名を転記します。
⑩ ワークシート「Log」のF列に使用しているPC名を転記します。
⑪ ブックの保存を行います（履歴の保存）
【プログラムの実行条件】
●今回のプログラムについては、ワークシートのイベントプロシージャを利用してプログラムを実行します。プログラムは、Sheet1（社員情報）に登録します。
また、今回のプログラムについては、２つのイベントプロシージャを利用していいます。
●ワークシート名「LOG」を作成します。このシートに変更履歴等が記録されます。
● Private Sub Worksheet_SelectionChange(ByVal Target As Range)
・ワークシート上で選択範囲を変更した時に呼び出されます。（※変更前のセルの値を保持するのに使用します。）
● Private Sub Worksheet_Change(ByVal Target As Range)
・ワークシート上のセルの値が変わった時に呼び出されます。（※変更後の履歴情報を作成する時に使用します。）
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● WorkSheets_LOG02
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
' ' Option Explicit Dim OldRange As String Dim XCol, YRow, lRow As Long Dim WsnObj As Object ' ' Private Sub Worksheet_Change(ByVal Target As Range) Set WsnObj = CreateObject("WScript.Network") 'WshNetworkオブジェクトを利用してユーザー名・コンピューター名を取得します。 XCol = Target.Column '列番号を取得します。 YRow = Target.Row '行番号を取得します。 lRow = Worksheets("LOG").Range("A" & Rows.Count).End(xlUp).Row + 1 'ワークシート「LOG」A列の最終行を取得します。 With Worksheets("LOG") .Range("A" & lRow) = Format(Now(), "YYYY/MM/DD HH:MM:SS") '更新日時をワークシート「LOG」A列に日時を転記します。 .Range("B" & lRow) = Target.Address(YRow, XCol) '変更位置（セル番号）をワークシート「LOG」B列にセル番号を転記します。 .Range("C" & lRow) = OldRange '変更前の内容をワークシート「LOG」C列に転記します。 .Range("D" & lRow) = Cells(YRow, XCol) '更新後の内容をワークシート「LOG」D列に転記します。 .Range("E" & lRow) = WsnObj.UserName 'コンピューターのユーザー名をワークシート「LOG」E列に転記します。 .Range("F" & lRow) = WsnObj.ComputerName 'コンピューター名をワークシート「LOG」F列に転記します。 End With ActiveWorkbook.Save 'アクティブブックを保存します。（履歴保存） End Sub ' ' Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next 'エラー発生を無視する OldRange = Target.Value '変更前のセル内容を一時保管します。 End Sub ' |
（画面クリックして拡大）
EXCEL VBA EXCEL操作のログ（記録）をブック単位に記録する・新規作成したシート・複数シートも操作ログを記録
下記のサンプルプログラムは、サンプルプログラム②の応用になります。サンプル①②については、指定したシート「社員情報」のみ操作ログを記録する出来るようにしていましたが、今回のプログラムでは、シート「LOG」以外のシートに追加・変更等を行うとシート「LOG」に操作ログとして記録されます。また、新規にワークシートを作成してもそのシートも記録する事が出来ます。ログに記録する内容については、更新日時・シート名、変更セル番号・更新前・更新後・コンピューターユーザー名・コンピュータ名です。
【プログラム処理手順】（ワークシート「LOG」以外の内容が変更された場合に実行）
① 変更前のセル内容を一時保管します。
② WshNetworkオブジェクトを利用してユーザー名・コンピューター名を取得する準備を行います。
③ セルが変更された列番号・行番号を取得します。
④ ワークシート「Log」A列の最終行を取得します。※Logに追記するため最終行を取得
⑤ ワークシート「Log」のA列に現在の日時を転記します。
⑥ ワークシート「Log」のB列に変更したワークシート名を転記します。
⑦ ワークシート「Log」のC列に変更位置（セル番号）を転記します。
⑧ ワークシート「Log」のD列にセルの変更前①の内容を転記します。
⑨ セルを変更したセルの書式設定を取得します。
⑩ 取得したセルの書式設定を変更前に記録するセルDと変更後に記録するセルEの書式設定に反映します。
⑩ ワークシート「Log」のE列に変更後のセル内容を転記します。
⑪ 内容が日付の場合は、セルの書式を日付形式に設定し内容を変更後のセルに転記します。また、変更後のセル内容が日付では無い場合は、セルの書式設定を標準にします。
⑪ ワークシート「Log」のF列に使用しているPCのユーザー名を転記します。
⑫ ワークシート「Log」のG列に使用しているPC名を転記します。
⑬ ブックの保存を行います（履歴の保存）
【プログラムの実行条件】
●今回のプログラムについては、ワークシートのイベントプロシージャを利用してプログラムを実行します。プログラムは、ThisWorkbookに登録します。
また、今回のプログラムについては、２つのイベントプロシージャを利用しています。（詳細は下記の参照して下さい。）
●ワークシート名「LOG」を作成します。このシートに変更履歴等が記録されます。
● Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
・ワークシート（ブック）上で選択範囲を変更した時に呼び出されます。（※変更前のセルの値を保持するのに使用します。）
● Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
・ワークシート（ブック）上のセルの値が変わった時に呼び出されます。（※変更後の履歴情報を作成する時に使用します。）
★【サンプルプログラム】
下記のリンク先よりサンプルプログラムをダウンロードする事ができます。
● WorkSheets_LOG05
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
' ' Option Explicit Dim OldRange, SheetName As String Dim XCol, YRow, lRow As Long Dim WsnObj As Object Dim formats As Variant Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Set WsnObj = CreateObject("WScript.Network") 'WshNetworkオブジェクトを利用してユーザー名・コンピューター名を取得します。 Application.EnableEvents = False 'イベントの無効化 If ActiveSheet.Name <> "LOG" Then 'シート「LOG」以外で実行します。（ログを記録します。） XCol = Target.Column '列番号を取得します。 YRow = Target.Row '行番号を取得します。 lRow = Worksheets("LOG").Range("A" & Rows.Count).End(xlUp).Row + 1 'ワークシート「LOG」A列の最終行を取得します。 With Worksheets("LOG") .Range("A" & lRow) = format(Now(), "YYYY/MM/DD HH:MM:SS") '更新日時をワークシート「LOG」A列に日時を転記します。 .Range("B" & lRow) = Sh.Name '更新したシート名をワークシート「LOG」B列に転記します。 .Range("C" & lRow) = Target.Address(YRow, XCol) '変更位置（セル番号）をワークシート「LOG」C列にセル番号を転記します。 .Range("D" & lRow) = OldRange formats = Target.NumberFormat '変更したセルの書式設定を取得します。 If Not IsEmpty(formats) Then .Range("D" & lRow).NumberFormat = formats '所得したセルの書式設定を変更前Dセルに設定します。 .Range("E" & lRow).NumberFormat = formats '所得したセルの書式設定を変更後Eセルに設定します。 End If .Range("E" & lRow) = Cells(YRow, XCol) '更新内容をワークシート「LOG」E列に転記します。 .Range("F" & lRow) = WsnObj.UserName 'コンピューターのユーザー名をワークシート「LOG」F列に転記します。 .Range("G" & lRow) = WsnObj.ComputerName 'コンピューター名をワークシート「LOG」D列に転記します。 End With ActiveWorkbook.Save 'アクティブブックを保存します。（履歴保存） End If Application.EnableEvents = True 'イベントの有効化 End Sub ' ' Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next 'エラー発生を無視する OldRange = Target.Value '変更前のセル内容を一時保管します。 End Sub ' ' |
（画面クリックして拡大）
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。