はじめに

こんにちは。
キャスレーコンサルティング IT(インテグレーションテック)部の村田(光)です。

現在、多くの開発現場では設計書などのドキュメントを作成する際、Excelを利用しています。
Wordでは目次を自動生成してくれる便利な機能がありますが、残念ながらExcelではそのような機能は無く、Excelで目次を作成したい場合、ハイパーリンク機能などで作成していくしかありません。

手作業で目次を作成した場合、追加・修正の度に目次も見直す必要があり、非常に効率が悪いです。
そこで、目次シートを自動生成するボタンをVBAを用いて作ってみましたのでご紹介いたします。
※本ブログは「Excel2013」を使用しています。

目次

1.目次生成ボタンの作成方法
2.目次生成機能の実装(VBA)
3.目次生成ボタンの実行
4.まとめ

1.目次生成ボタンの作成方法

①開発タブを押下
②挿入ボタンを押下
③ActiveXコントロールのボタンアイコンを押下

上記を実行するとカーソルが「+」になるので、適当なところでクリックすると下記のようなボタンができます。

これでボタンの作成は終わりです。

2.目次生成機能の実装(VBA)

上記の方法でボタンを作成し、その状態で作成したボタンをダブルクリックをすると、下記の画面が表示されます。

上記の画面が表示されたら、下記のコードをそのまま貼り付けることで簡単にツールを作成できます。

Private Sub CommandButton1_Click()
 
    Dim i       As Integer
    Dim iRow    As Integer
    Dim sName   As String
 
    '目次シートの設定内容をクリア
    Range("A9:BC65535").ClearContents
    Range("A9:BC65535").Hyperlinks.Delete
 
    '目次開始行数(本例は8行目から目次が作られる)
    iRow = 8
    '目次を作成する列数(本例は3列目(C列)に目次が作られる)
    iColumn = 3
 
    'ワークシートのサイズ分、下記処理を繰り返す
    For i = 1 To Worksheets.Count Step 1
        '非表示となっているワークシートは目次作成対象外とする
        If Worksheets(i).Visible = xlSheetVisible Then
            'ワークシート名を格納
            sName = "'" & Worksheets(i).Name & "'"
            '目次シートの対象セルにハイパーリンクを設定(目次作成対象ワークシートのA1セルへのリンク)
            Call Hyperlinks.Add(Anchor:=Cells(iRow, iColumn), Address:="", SubAddress:=sName & "!A1", ScreenTip:=Worksheets(i).Name)
            '目次シートの対象セルにシート名を設定
            Cells(iRow, iColumn) = Worksheets(i).Name
 
           'リンクの文字の大きさ、フォントを設定
            Cells(iRow, iColumn).Font.Size = 13
            Cells(iRow, iColumn).Font.Name = "MS ゴシック"
            Cells(iRow, iColumn).Font.Bold = True
 
            '次の行へ
            iRow = iRow + 1
        End If
    Next i
 
End Sub

3.目次生成ボタンの実行

では、実際にボタンを動かしてみましょう。
まずは目次シートとその他のワークシートを5枚用意します。

この状態で目次生成ボタンを押下すると、目次が自動生成されます。

次にシート1の名前を変更し、シート3を削除した状態で実行してみます。
下記がワークシート名変更・ワークシート削除後の状態です。

この状態で目次生成ボタンを押下すると、ワークシート名の変更やワークシートの削除が適用され、目次が最新化されます。

4.まとめ

いかがだったでしょうか。
今回のツールはワークシートのハイパーリンク作成から削除まで自動で行ってくれるので、作業効率が上がります。

今後、Excelで目次を作成する際には、是非使用してみて下さい。
ご一読いただき、ありがとうございました。

村田(光)
CSVIT事業部IT部 村田(光)
新卒入社1年目のエンジニア。通信事業者の申込管理システム開発に日々奮闘中。