VBA・マクロツール

【知らなくても使える】シート毎の更新された日時を各シートに書き込むマクロツール(Excel VBA)

 

Excelファイルをシート毎に更新日時を表示させたいと思ったことはないでしょうか。

 

Excelファイルの更新日時はブック単位でしか更新日時をとることができず、一つのファイルを複数の人で更新している場合などに、シート毎に最終更新した日時を記録として残しておきたいと考えたので、このシート毎に更新日時を記録しておけるツールをExcelマクロで作成しました。

 

このツールを使用することで、各シートで更新情報を分けることができて、ブック全体でなく、もう少し細分化した単位であるシート毎に管理ができるようになると思います。

 

是非、当ツールをご活用頂けたらと思います。

 

当ツールの概要(何ができるのか?)

更新日時を表示させたいエクセルファイル内で設定画面(設定シート)を用意して、設定画面に更新日時を記載するセルを指定(全シート共通、またはシート毎に記載するセル位置を分ける)します。

 

後は、設定画面以外のシート内の情報を変更して、保存することで指定したセルに更新日時を自動的に記載してくれます。

 

下に説明動画を載せますので、ご確認ください。動画を見ると分かりやすいと思います。

 

<説明動画> ※映像のみで音声はありません。

 

ツールの使用方法

基本的な機能、操作方法の説明

1.更新日時をシート毎に管理したいExcelファイル(当ツールのマクロ付き)を開きます。

※当ツールのVBAコードを含んでいて、「setting」シート(設定情報を書き込めるシート)がファイル内に存在している状態のファイルです

 

2.「setting」シートに更新日時を記載するセル位置を記載します。

各シート毎に更新日時を表示するセル位置を変える場合は、個別指定するシート名とセル位置をそれぞれ記載します。

 

3.「setting」シート以外のシートでセル内の値を変更して、ファイルを保存すると。

 

指定したセル位置「C1」に更新日時が表示されます

 

また、個別指定したシート内のセルを変更してファイルを保存した場合は、

 

個別に指定したセル「C2」に更新日時が表示されます

 

しかし、他のシートには表示されていた更新日時は変更されません。

 

なお、「設定」シートは、シート自体を非表示にしても動作しますので、他の人に見せる必要がない場合は、シートを非表示にしておくことをお勧めします

 

また、更新日時だけでなく、「変更前の値」や「変更後の値」、「更新者情報」などもVBAコードを少し変更することでセルに表示させることができますので、必要に応じてカスタマイズして頂ければと思います。

 

留意事項

更新の判断基準

このツールでの変更の判断は、セル内の値に変更があった場合に更新されたと判断して、更新日時を表示させます。

そのため、セル色の変更や文字色の変更のみをした場合などは更新されたと判断しないため、ファイルを保存しても更新日時は変更されません。

 

使用する際の事前準備

このページの下部にある「Excelファイルのサンプル」からExcelファイルを取得し、「準備の手順」内にあるVBAコードを取得したExcelファイルに記載して保存すれば、すぐに当ツールを使用できます。

 

以下にその手順を説明していきます。

 

準備の手順

1.「サンプルのダウンロードはこちら」からサンプル(Excelファイル)をダウンロードします。

※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、設定画面(設定シート)が入っているマクロ無しExcelファイルを公開しています

 

2.ダウンロードしたExcelファイル内にある「setting」シートを更新日時を管理したいExcelファイルにコピーします。

 

3.2でコピーしたExcelファイルの上部にあるリボンの「開発」タブから「visual basic」を選択して、VBE(visual basic for application)を起動させます。

 

リボンに「開発」タブが表示されていない場合は、以下を参照ください。

[nlink url=”https://resthill.blog/excel-vba0/#Excel”]

 

4.「プロジェクト」内の「ThisWorkbook」を選択して、表示されたエディタ(左側の欄)にVBAコードを記載します。

 

VBAのソースコードはこちら

以下のVBAコードをコピーして、「ThisWorkbook」内のエディターに貼り付けます。

※VBAコードの右上のアイコンをクリックするとソースコードをコピーできます

Option Explicit

'-----(設定値)------------------------
Private Const DATA_ROW = 7                      '1.設定シート内の設定表が開始する行番号
Private Const DATA_COL_NUM = 3                  '2.設定シート内の設定表が開始する列番号
Private Const DATA_EACH_COL_NUM = 5             '3.設定シート内の設定表(個別設定)が開始する列番号
Private Const SETTING_SHEET_NM = "setting"      '4.設定シートのシート名称
'---------------------------------------

Dim SettingSheet As Worksheet                   '設定シート

Dim BeforeValue As String                       'セル内に入っている変更前の値
Dim TRow As Long, TCol As Long                  '選択されたセルの行番号と列番号
    
Dim TgtCellPoint As String                      '一括設定のセル位置
Dim TgtEachlist() As String                     '個別設定情報が格納されたリスト(0:シート名、1:セル位置)
Dim TgtUpdateList() As String                   '変更したシート等の情報が格納されたリスト(0:シート名、1:セル位置、2:更新日時)
Dim CntChgSh As Integer                         '変更したシートの数

'===================================
'エクセル起動時に実行する処理
'===================================
Private Sub Workbook_Open()
    
    '初期処理をする
    Call Initialize
    
    '設定シートの情報を取得する
    Call GetSetting
    
End Sub
 
'===================================
'エクセル内のセル等を選択した時の処理
'===================================
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 
    On Error Resume Next
    
    '変更前のセル内容を一時保管します。
    BeforeValue = ActiveSheet.Cells(ActiveCell.Row, ActiveCell.Column).Value
    
End Sub

'===================================
'エクセル内の値を変更した時の処理
'===================================
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Application.EnableEvents = False
    
    '配列が空の場合はTgtUpdateListリストを作成する
    If IsEmptyArray(TgtUpdateList) Then
        Call Initialize
    End If
    
    '設定情報を取得する
    Call GetSetting
    
    Dim i As Long
    Dim exitFlg As Boolean: exitFlg = False
    
    'シート「setting」以外のシートで変更があった場合のみ処理する
    If ActiveSheet.Name <> SETTING_SHEET_NM Then
 
        TCol = Target.Column
        TRow = Target.Row
        
        '値が変更されていた場合のみ処理する
        If BeforeValue <> ActiveSheet.Cells(TRow, TCol).Value Then

            '既に変更されたデータが格納されていないかチェックする
            For i = 0 To UBound(TgtUpdateList, 1)

                If TgtUpdateList(i, 0) = ActiveSheet.Name Then
                    exitFlg = True
                    Exit For
                End If
            Next

            If exitFlg = False Then

                ReDim Preserve TgtUpdateList(2, CntChgSh)

                '個別設定があれば、そのセル位置を取得する
                For i = 0 To UBound(TgtEachlist, 1)

                    If ActiveSheet.Name = TgtEachlist(i, 0) Then
                        TgtUpdateList(1, CntChgSh) = TgtEachlist(i, 1)                 'セル位置
                        Exit For
                    End If
                Next

                '個別設定がなければ、一括設定のセル位置を取得する
                If TgtUpdateList(1, CntChgSh) = "" Then
                    TgtUpdateList(1, CntChgSh) = TgtCellPoint                          'セル位置
                End If

                TgtUpdateList(0, CntChgSh) = ActiveSheet.Name                          'シート名
                TgtUpdateList(2, CntChgSh) = Format(Now(), "YYYY/MM/DD HH:MM:SS")      '更新時間

                CntChgSh = CntChgSh + 1

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

'===================================
'エクセル保存した際の処理
'===================================
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim i As Long
    
    '配列が空かどうかチェックする(空の場合はTrueを返す)
    If IsEmptyArray(TgtUpdateList) Then Exit Sub
    
    '変更情報があれば指定されたセルに更新情報を記載する
    If TgtUpdateList(2, 0) <> "" Then
        For i = 0 To UBound(TgtUpdateList, 2)

            ActiveWorkbook.Sheets(TgtUpdateList(0, i)).Range(TgtUpdateList(1, i)).Value = Format(Now(), "YYYY/MM/DD HH:MM:SS")
        Next
    End If

    CntChgSh = 0
    ReDim TgtUpdateList(2, CntChgSh)

End Sub

'-------------------------
'初期処理をする
'-------------------------
Private Sub Initialize()

    Set SettingSheet = ActiveWorkbook.Sheets(SETTING_SHEET_NM)
    ReDim TgtUpdateList(2, 0)
    
End Sub

'-------------------------
'設定シートの情報を取得する
'-------------------------
Private Sub GetSetting()

    Dim lastRow As Integer
    Dim i As Long
    
    '一括設定で指定された更新日時を記載するセル位置を取得する
    TgtCellPoint = SettingSheet.Cells(DATA_ROW, DATA_COL_NUM).Value
    
    '個別設定データの最終行を取得する
    lastRow = SettingSheet.Cells(Rows.Count, DATA_EACH_COL_NUM).End(xlUp).Row
    
    If lastRow < DATA_ROW Then
        Exit Sub
    End If
    
    ReDim TgtEachlist(lastRow - DATA_ROW, 2)
    
    '個別設定で指定されたシート名と記載セル位置を取得する
    For i = DATA_ROW To lastRow
        
        'シート名に値があり、記載セル位置に値がない場合は、該当行の文字色を赤色にする
        If SettingSheet.Cells(i, DATA_EACH_COL_NUM).Value <> "" And SettingSheet.Cells(i, DATA_EACH_COL_NUM + 1).Value <> "" Then
            
            TgtEachlist(i - DATA_ROW, 0) = SettingSheet.Cells(i, DATA_EACH_COL_NUM).Value
            TgtEachlist(i - DATA_ROW, 1) = SettingSheet.Cells(i, DATA_EACH_COL_NUM + 1).Value
            SettingSheet.Cells(i, DATA_EACH_COL_NUM).Font.ColorIndex = 1                        '個別設定表の項目「シート名」の文字色を黒にする
            SettingSheet.Cells(i, DATA_EACH_COL_NUM + 1).Font.ColorIndex = 1                    '個別設定表の項目「記載位置」の文字色を黒にする
        Else
            SettingSheet.Cells(i, DATA_EACH_COL_NUM).Font.ColorIndex = 3                        '個別設定表の項目「シート名」の文字色を赤にする
            SettingSheet.Cells(i, DATA_EACH_COL_NUM + 1).Font.ColorIndex = 3                    '個別設定表の項目「記載位置」の文字色を赤にする
            
        End If
    Next

End Sub

'-------------------------
'配列が空かチェックする
' 空の場合はTrueを返す
'-------------------------
Private Function IsEmptyArray(tmpArry As Variant) As Boolean
    
On Error GoTo err
    
    'UBound関数を使用してエラーが発生するかで確認する
    If (0 < UBound(tmpArry, 1)) Then
        IsEmptyArray = False
    Else
        IsEmptyArray = True
    End If
    
    Exit Function

err:
    'エラーが発生した場合(空の場合)はTrueを返す
    IsEmptyArray = True
    
End Function

 

5.作成したExcelファイルを「.xlsm」形式(マクロが動作するファイル形式)で保存します。

 

これで事前準備は完了です

あとは「基本的な機能、操作方法の説明」で記載された方法でツールを実行できます。

 

なお、当マクロの開発環境は、OS:Windows10 、Excelソフトウェア:Microsoft Office 365となっており、当環境では動作確認ができていますが、他の環境で正常に動作するかは確認できていません。

正常に動作しない場合は、コメントいただければ幸いです。

 

 

また以下にて、このような業務効率化できるツールを機能ごとの一覧でまとめてますので、ご興味のある方はご覧ください。

 

自力で業務効率化できるツール等を作成する場合は、オンラインITスクール」を利用するとモチベーションを保ちつつ、効率的に学習とアウトプットができると思います。

\ 今なら1か月間全額返金保証!! /

上記の「侍テラコヤ」月額2,980円~ という日本最安級の料金でプログラミング学習ができ、今なら初めての方でも安心できる「1か月全額返金保証」があります

自分に合わないと感じた場合は返金してもらえるため、ノーリスクで試すことができます。是非一度体験してみることをオススメします ^ ^

 

ツールのダウンロードはこちら

下記よりExcelファイルをダウンロードして、記事の途中にありました VBAのソースコードをツール内に組み込んで使用してください。

【Excel VBA】各シートへの更新日時表示ツール

ツールのダウンロードはこちら

※インターネットにあるマクロファイルをダウンロードすることはセキュリティ上リスクがあるので、マクロ無しExcelファイルを公開しています

他に要望等ありましたら、可能な限り改修等を対応しますのでコメント頂ければと思います

 

<このツールが『結構使える!』と思ったら、下のグッドボタンを押していただけたら幸いです>

コメントはこちら

  1. hi より:

    コメント失礼いたします。
    こういったツールを探しており大変参考になりました。
    1点お聞きしたいのですが、こちらのツールはOneDriveの自動更新には対応していないでしょうか。
    よろしければご回答頂けますと幸いです。
    宜しくお願いいたします。

    • RH より:

      管理人のRHです。
      hiさん、コメントいただき、ありがとうございます。

      こちらのツールはOneDriveの自動保存時にも対応しております。
      シート内で変更されたセルがあった場合は、自動保存時に指定された箇所に更新日時が記載されます。

      なお、VBAコードとアップロードされている設定シートを含んだExcelファイルを少々修正しました。

      もし動作しない場合等ありましたら教えていただければ幸いです。