エクセル多用途もくじシートを自動作成するVBA

vbatoclisteyecatch

エクセルVBAでハイパーリンク型では難しい、多目的に利用できる目次シートの自動作成です。
シートを改訂・変更・内容検索など、今回はシート削除機能を付加します。

こんにちは じゅんぱ店長 (@junpa33) です。

日々の業務で、溜まりに溜まった渦高いエクセルシート。

中にはシート名「〇〇〇〇(5)」とか「この子は一体誰のコピー」なんて非常に” グチャっ ”となっていることもあるかもしれません。

ちゃんとしたシート名は最低付けてあげましょう。こんなエクセルBOOKたくさん持っていませんか?

今回は、そんな大量のシートが詰まったエクセルBOOKを整理するためのツールをVBAでつくってみましょう!ということです。

そのエクセルファイルに、新たに「シートの目次」シートを自動設置し、一覧表管理からの多目的利用を出来るようにします。

一覧表は何度も更新できますので、不要なシートを整理整頓削除を行っても大丈夫です。

ちなみに、目次機能だけですが、別記事でシートリンクタイプの「シート目次」も紹介しますので、そちらもまた参考にしてください。

「多機能シート目次」エクセルソフトのダウンロード

vbatoclistp017

完成VBAソフトはこちらからダウンロードできます。

このエクセルソフトは以下からダウンロードすることができます。ダウンロード頂いた「多機能シート目次」はライセンス無料で使用いただけます。

ダウンロードはこちらから↓

このソフトはご自分で業務でお使いいただくのはフリーですが、

転載や転売については許可しておりませんので、ご使用にならないよう固くお断りいたします。

違法ルートで入手された場合は、著作権に抵触します。

This software is free to use for your own business,

Reproduction or resale is not permitted, so please refrain from using it.

It violates copyright if it is obtained through illegal routes.

多目的利用の「もくじシート」を作成する意味

エクセルシートが大量に詰まったエクセルBOOKに目次一覧をハイパーリンクで作るというのは見かけます。

自分も一つのエクセルBOOKにシート数30(1ヶ月分)なんて普通にあります。

実際、ハイパーリンク目次を設置してもいますが、それはシートを表示するだけのもの。それが出来るだけの事でしかありません。

「で、それで・・」が次にありません。

使い道的には、目次だけを作っても次の段階の整理という作業につなげていき辛いです。

それは結局、シートの整理作業は、以前からの手作業のまま、何にも変わらないからです。

そこで、「多用途型目次シート」作成に至った訳ですが、

この「多用途型目次シート」作成の意味は、目次からのシート表示に続いて、その流れで、シートを改訂・変更・内容検索や削除などを出来るようにするためのベースのシートとなるものです。

今回作成するのは、シートを選択して、内容チェックで要らないなら即削除できるようなプログラムです。

もくじ一覧表を利用したシート整理ツールの1パターンを紹介していきます。

既存のエクセルBOOKに埋め込みますので、実際、プログラム起動初期では、ユーザーフォームだけが追加されるイメージです。

今回は、不要シートの整理に着目しています。削除機能シート目次一覧新設更新機能を付けています。

  1.  シート名一覧からそのページに飛んで、不要なシートを「ワンクリック削除」
  2.  削除した場合は逆ジャンプ機能で自動的にシート目次一覧に戻ることが出来る
  3.  削除したシートを排除したシート目次一覧に即更新することが出来る

というようなものに仕上げます。

もくじシートを自動増設するVBA

「多機能シート目次.xlsm」VBAプログラムを作るために、ザックリ作業種類を書くと、

  •  標準モジュールにプログラム本体のVBAコードを張り付ける
  •  プログラムのコントロールを行うユーザーフォームを作成する
  •  本プログラムの設置先エクセルBOOKのブックモジュールに、ユーザーフォームの視覚化コードを設置する

の様になります。

プログラムをいずれかのエクセルBOOKに移植する場合に、必要なことと、必要でないこと

  • <必要なこと>「そのエクセルBOOKを、マクロ有効ブック(拡張子を.xlsm)にして変更保存することです。」
  • <必要のないこと>「プログラムを埋め込んだエクセルBOOKの名称を変更する必要はありません。」

標準モジュールにペーストする本体のVBAコード

コピペの方法はこちら↓です。

vbacopipeeyecatch Webで見つけたマクロをコピペで使う

標準モジュールにプログラム本体のVBAコードを張り付ける全コードです。

実行するためのコマンドは全て、ユーザーフォームのボタンで行います。

VBA
Option Explicit

'解説1
Function Flag() As Boolean
    Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = "もくじシート" Then
                Flag = True
                Exit For
            Else
                Flag = False
            End If
        Next
End Function

'解説2
Sub シート一覧()
    Dim s, i, BorRow As Long
    Dim mySheetName() As String
'解説2-1
        If Flag = False Then
            On Error Resume Next
            Application.VBE.Windows(1).SetFocus
            With ThisWorkbook
                .Worksheets.Add(before:=Worksheets(1)).Name _
                            = "もくじシート"
                .VBProject.VBComponents(Worksheets("もくじシート").CodeName) _
                            .Name = "もくじシート"
            End With
            Application.VBE.MainWindow.Visible = False
'解説2-2
            If Err.Number = 9 Then
                Application.DisplayAlerts = False
                Worksheets("もくじシート").Delete
                Application.DisplayAlerts = True
                On Error GoTo 0
                MsgBox "起動エラーが発生しました。"
                Exit Sub
            End If
            Call Module1.UFリカバーCore
        End If
'解説2-3
        Worksheets("もくじシート").Select
        With Worksheets("もくじシート")
            With .Cells
                .Clear
                .Font.Bold = True
                .Font.Size = 13
            End With
            .Columns(1).HorizontalAlignment = xlCenter
            .Range("A2") = "NO."
            .Range("B2") = "シート名称"
            .Range("A1") = "シートもくじからそのシートにジャンプできます。"
            With .Range("A1:B2")
                .Font.ColorIndex = 5
                .Font.Size = 15
            End With
        ReDim mySheetName(1 To Worksheets.Count)      '
            For i = 1 To Worksheets.Count
                mySheetName(i) = Worksheets(i).Name
                .Range("A" & i + 2) = i
                .Range("B" & i + 2) = mySheetName(i)
            Next i
            With .Cells(1, 1).CurrentRegion
                BorRow = .Rows(.Rows.Count).Row
                .Offset(1).Resize(BorRow - 1, 2).Borders.LineStyle = True
                .Columns(2).AutoFit
            End With
            With .Range("A1")
                .HorizontalAlignment = xlLeft
                .Select
            End With
        End With
End Sub

'解説3
Sub シートジャンプ()
    Dim shRow As Long
    Dim SV As String
    Dim ws As Worksheet
    Dim Flagn As Boolean
        If Flag = False Then
            MsgBox "もくじシートがまだ作成されていません。"
            Exit Sub
        End If
            Worksheets("もくじシート").Select
            shRow = Cells(Rows.Count, 2).End(xlUp).Row
            If ActiveCell.Row < shRow + 1 And ActiveCell.Column = 2 Then
                SV = ActiveCell.Value
                If SV = "もくじシート" Then
                    Exit Sub
                Else
                    For Each ws In Worksheets
                        If ws.Name = SV Then Flagn = True
                    Next ws
                    If Flagn = True Then
                        Worksheets(SV).Select
                        Range("A1").Select
                    Else
                        MsgBox SV & " シートは存在しません。", _
                                    vbInformation, "メッセージ"
                    End If
                End If
            End If
End Sub

'解説4
Sub シートを削除()
    Dim ActSh As String
    Dim Ans As Long
    Dim DelRow, n As Long
        If Flag = False Then
            MsgBox "もくじシートがまだ作成されていません。"
            Exit Sub
        End If
        ActSh = ActiveSheet.Name
        Ans = MsgBox("削除しようとしているシートは、「 " _
            & ActSh & " 」です。" & vbCrLf & "削除しますか?", _
            vbYesNo + vbExclamation, "削除許可申請")
        If Ans = vbNo Then
            Exit Sub
        ElseIf Ans = vbYes Then
            If ActSh = "もくじシート" Then
                MsgBox "もくじシートは削除出来ません。"
            Else
                Worksheets(ActSh).Delete
                Worksheets("もくじシート").Select
                With Cells(1, 1).CurrentRegion
                    DelRow = .Rows(.Rows.Count).Row
                End With
                For n = 3 To DelRow
                    If Cells(n, 2) = ActSh Then
                        Cells(n, 2).ClearContents
                    End If
                Next n
            End If
        End If
End Sub

'解説5

Sub UFリカバーCore()
    Dim wb As Workbook
    Set wb = ThisWorkbook
    With wb.VBProject.VBComponents
        .Item("もくじシート").CodeModule.AddFromString _
            "Private Sub Worksheet_Activate()" & vbCrLf & _
            "   If 目次コントロール.Visible = False Then" & vbCrLf & _
            "       目次コントロール.Show vbModeless" & vbCrLf & _
            "   End If" & vbCrLf & _
            "End Sub"
    End With
End Sub

解説1(ファンクションプロシージャー「Flag」)

Functionプロシージャーを使って、エクセルBOOKに「もくじシート」が作成されているかどうかをチェックしています。

作成されていれば「True」、いなければ「False」が変数「Flag」に代入されます。

VBAFunctioneyecatch Functionプロシージャーとユーザー定義関数 sheetexistanceeyecatch シートの存在を確認する2種類のコードと実務での例題

解説2(「シート一覧」プロシージャー)

このプロシージャーは「もくじシート更新」ボタンに関連付けされます。

  1. 「もくじシート」が作成されていなければ、新規にシートをシートタブの左端に増設して「もくじシート」を作成します。
  2. 作成するシート名を「もくじシート」にするだけでなく、オブジェクト名も「もくじシート」に設定します。
  3. オブジェクト名を「もくじシート」に変更できなかった時のエラー対策を行います。
  4. 「UFリカバーCore」プロシージャーを呼び出します。
  5. 「もくじシート」が作成されていればスルーです。
  6. 左端からシートのタブ順に従って、リスト化します。
  7. 罫線を引いて一覧表を作成します。

ここでのVBAの完成目標

「もくじシート」がない場合は「もくじシート」が作成されます。

「もくじシート」作成時に同時に自動で、シートモジュールにユーザーフォームの表示コードが記述されます。

目次リストの作表が完成します。

解説2-1

「もくじシート」がない場合にもくじシートを作成します。

  • 新規シートを追加しますが、シート名だけでなくオブジェクト名も「もくじシート」とします。
  • オブジェクト名を変更する(デフォルトの名前から「もくじシート」へ)ので、少々のコードテクニックが必要です。
  • コードエラーで停止(エラーナンバー9)の可能性もあり、対策コードも記述する必要があります。
  • オブジェクト名を変更するために
  • 一時的にエラーを回避(無視)するコードを記述します。
  • VBEを立ち上げます。(エクセル起動だけでは立ち上がらない)
  • 新規作成のシート名を「もくじシート」にします。
  • シート名「もくじシート」のオブジェクト名を変更します。
  • VBEの表示を終了させます。

「もくじシート」作成時に起動するVBAコードの部分ですが、

PCのその時の利用状態によって、このコードを拾いきれない(実行されない)場合があるようです。

このコードでエラーが発生(オブジェクト名が変更されない)します。

「エラーコード9」のエラーが多いです。

このコード実行時にVBEが表示されていないときに発生していますので、対策コードを入れています。

他にもエラー原因が発生する可能性もありますので、対処療法として ” 解説2-2 ” のコードを加えています。

VBA
            With ThisWorkbook
                .Worksheets.Add(before:=Worksheets(1)).Name _
                            = "もくじシート"
                .VBProject.VBComponents(Worksheets _
                    ("もくじシート").CodeName) .Name = "もくじシート"
            End With
利用上の注意点

「もくじシート」を強制的に削除(シートタブの右クリックメニューで削除)した後も含めて、

新たに「もくじシート」が作成されるときに、ユーザーフォームが消えてしまいます。

その場合は、「もくじシート」をアクティブにすれば再度ユーザーフォームが表示されます。

「もくじシート」も無く「ユーザーフォーム」表示も消えている場合は、エクセルBOOKを再起動してください。

解説2-2

エラーの発生内容は、

新規シートで「もくじシート」が追加されますが、そのオブジェクト名がデフォルトの自動割り当て名称のままで「もくじシート」に変更できなかった。

という内容になります。

このエラーが発生した時に自動的に元の状態に戻すコードを埋め込んでいます。

エラーが発生した場合には、メッセージ「起動エラーが発生しました。」を表示します。

エラーが発生しなかった場合は、「もくじシート」にユーザーフォームを表示するためのコードを自動記述します。

解説2-3

「もくじシート」に目次リストを作表します。

シート名をインデックス番号で所得しセルに順番に入れていきます。

vbawithstateeyecatch With~End Withの使い方。VBAコードを簡潔に記述する fornextirekoeyecatch For~Nextのループと入れ子構造をVBA最速理解 vbahairetueyecatch 1次元配列とは。静的配列と動的配列 エクセルVBA VBAcellareaeyecatch セルの範囲選択と設定は14のツールで対応する vbacellsbordereyecatch 「罫線」のVBAを最速理解 vbacalleyecatch 部品化プロシージャーでCallステートメントは必須

解説3(「シートジャンプ」プロシージャー)

このプロシージャーは「シート表示」ボタンに関連付けされます。

「もくじシート」が作成されていなければ、「もくじシートがまだ作成されていません。」とメッセージが表示されこのプログラムは停止します。

選択した「シート名」のシートに移動しそのシートを開きます。

シートの指定方法は、「シート名のセル」を選択してください。

「もくじシート」に戻る場合は、「もくじシート更新」が逆ジャンプボタンを兼用しています。

vbadoloopeyecatch VBA 回数不定のループ処理はDo LoopとFor Each

解説4(「シートを削除」プロシージャー)

このプロシージャーは「シート削除」ボタンに関連付けされます。

「もくじシート」が作成されていなければ、「もくじシートがまだ作成されていません。」とメッセージが表示されこのプログラムは停止します。

このプログラムを実行する前に、「本当に削除するかのメッセージ」が表示されます。「はい」「いいえ」で応答します。

「はい」をクリックした場合は、エクセルのデフォルトの機能の方から「削除警告メッセージ」が再度表示されますので、さらに「削除」で削除実行されます。(2重にクリックミスを防止します。)

「もくじシート」はこの方法では削除できません。

削除完了後、「もくじシート」に自動で逆ジャンプします。シート一覧表で、削除したシート名が記入されたセルは「空白」になっています。

「もくじシート更新」ボタンをクリックすると最新のリストに改訂されます。

step1
step2
step3
step4
vbaifjyokeneyecatch If条件文のVBAコードの組み方。条件の絞り方を最速理解 vbamsgboxeyecatch メッセージボックス MsgBox実際の使い方を最速に理解

解説5(「UFリカバーCore」プロシージャー)

新設した「もくじシート」にユーザーフォーム表示設定のコードを(作成時に)自動で記述します。

このコードで、「もくじシート」が生成された以降は、「もくじシート」にユーザーフォーム表示のスイッチの機能が付加されます。

ユーザーフォームを作成してプログラムをコントロール

ユーザーフォームに実行ボタンを設置することで、フローティングボタンとしてシート横断で常に表示されるので、使い勝手が非常によくなります。

ユーザーフォームオブジェクト

ユーザーフォームオブジェクトはコピペが出来ませんので、自力で作成してください。

【ユーザーフォーム】のプロパティ

  •  オブジェクト名  目次コントロール
  •  Caption      目次コントロール

【もくじシート更新】のプロパティ

  •  オブジェクト名  もくじシート更新
  •  Caption      もくじシート更新

【シート表示】のプロパティ

  •  オブジェクト名  シート表示
  •  Caption      シート表示

【シート削除】のプロパティ

  •  オブジェクト名  シート削除
  •  Caption      シート削除

ユーザーフォームモジュール

ユーザーフォームに配置したコマンドボタンと、標準モジュールにある本体VBAコードを接続するVBAコードを、ユーザーフォームモジュールに記述します。

ユーザーフォームオブジェクト作成モードからの遷移は赤矢印のボタンでモジュールモードになります。

ユーザーフォームモジュール用VBAコード
Option Explicit


'解説5
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        MsgBox "機能の性質上このボタンでは終了出来ません。"
        Cancel = True
    End If
End Sub

Private Sub シート削除_Click()
Call Module1.シートを削除
End Sub

Private Sub シート表示_Click()
Call Module1.シートジャンプ
End Sub

Private Sub もくじシート更新_Click()
Call Module1.シート一覧
End Sub

解説5

このVBAコードは、ユーザーフォームオブジェクトの右上肩にある「✖」を無効(ユーザーフォームを終了させない)にするためのコードになります。

理由ーー少々長くなりますが・・・

フーザーフォームを視覚化させるにはそのための設定コードが必要です。

通常、エクセルBOOKを立ち上げた時やシートを選択(アクティブに)した時に視覚化するようにセットします。

今回の想定では、「もくじシート」以外の全てのシートが削除可能な対象であり、また「もくじシート」もプログラム起動前では ”シートが存在しない” ということで、いずれのシートにも視覚化コードを設置することは出来ません。

ですのでつまりは、エクセルBOOKを起動したときに、視覚化するように仕組むことになります。

しかし一旦、この「✖」ボタンでユーザーフォームが閉じられてしまうと、このエクセルBOOKそのものを再起動しないとユーザーフォームが再表示されないことになってしまいます。これは大きなデメリットです。

故に

この「✖」ボタンを無効化して、エクセルBOOK起動中は常にユーザーフォームが表示されている状態にする必要があるのです。

ブックモジュールにユーザーフォーム視覚化コードを設置

最後は、ブックモジュールです。

プロジェクトエクスプローラーの「ThisWorkbook」をダブルクリックしてください。

ブックモジュール用VBAコード
Option Explicit

Private Sub Workbook_Open()
    目次コントロール.Show vbModeless
End Sub

「もくじシート」のシートモジュールには、シートの作成時に自動で以下のコードが記述されます。

VBA
Option Explicit

Private Sub Worksheet_Activate()
   If 目次コントロール.Visible = False Then
       目次コントロール.Show vbModeless
   End If
End Sub

以上でVBAコード配置等は終了です。

多用途利用もくじシートの設置 まとめ

みなさんが「こういう事をしたい」という場合、Webで探したコードで、プラン全体の中の半分はこれで出来る!という発見は多いでしょう。

(自分のやりたいことそのことすべてが見つかるなんてことはほぼ無いので、当然と言えば当然ですが・・・。)

例えば、このような目次一覧の場合なんかは、ハイパーリンクをVBAでコントロールするパターンが、ある意味メジャーな方法です。

確かに、ハイパーリンクを使うと非常に簡単に目次一覧を作成することが出来ます。

でも今回の目的の多用途化をはかる場合には、自分的には、逆にちょっと使いずらいかなという考えです。

単にシートに飛ぶだけの目次機能を目的とするのなら、ハイパーリンクはおススメできます。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう!

エクセルVBA業務ツールで日常の業務改善を行いましょう。

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。