全てのシートの一覧を作りたい、欲を言えば一覧にはハイパーリンクも付けたい …という要望があったのでマクロを作成しました!
シートの目次を作るマクロ(ハイパーリンク付き)
1. VBAのソースコード
ソースコードは以下になります。処理内容はソースのコメントに書いてあります。
ポイント
- メソッドが複数ありますが、実行するマクロはMakeMokujiSheetです
- COLNO_OUPTPUT_NOとROWNO_HEADERの数値を変更すると、目次の出力位置を変更できます。
- シート名は、SHEETNAME_MOKUJIという定数で設定しています。
copy
Private Const SHEETNAME_MOKUJI As String = "目次"
Private Const COLNO_OUTPUT_NO As Long = 2
Private Const COLNO_OUTPUT_SHEETNAME As Long = COLNO_OUTPUT_NO + 1
Private Const ROWNO_HEADER As Long = 2
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 目次シートを作成する(こちらのマクロを実行します)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub MakeMokujiSheet()
Dim sheet As Worksheet
' 目次シートの初期化
Call InitializeMokujiSheet
Set sheet = Worksheets(SHEETNAME_MOKUJI)
' 目次を作成
Call MakeMokuji(sheet)
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' "一覧"シートがある場合にはいったん削除して、1シート目に作り直す
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub InitializeMokujiSheet()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name = SHEETNAME_MOKUJI Then
' "削除しますか?"の確認メッセージを非表示にする
Application.DisplayAlerts = False
' "一覧"シートを削除する
Worksheets(SHEETNAME_MOKUJI).Delete
' メッセージを表示する設定に戻す
Application.DisplayAlerts = True
End If
Next ws
' 1シート目に"一覧"シートを作成する
Worksheets.Add Before:=Worksheets(1)
ActiveSheet.Name = SHEETNAME_MOKUJI
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 一覧を作成する
' テキスト:シート名、ハイパーリンク:各シートのA1セル
' 1シート目には"一覧"シートがあるため、2シート目から処理をする
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub MakeMokuji(ByVal sheet As Worksheet)
Dim sheetCount, rowNoOutputData As Integer
' ヘッダーを設定する
Call SetHeader(sheet)
' 一覧を出力する
rowNoOutputData = ROWNO_HEADER
For sheetCount = 2 To Worksheets.Count
rowNoOutputData = rowNoOutputData + 1
' Noを設定
sheet.Cells(rowNoOutputData, COLNO_OUTPUT_NO).Value = sheetCount - 1
' ハイパーリンクを設定
sheet.Hyperlinks.Add Anchor:= _
sheet.Cells(rowNoOutputData, COLNO_OUTPUT_SHEETNAME), _
Address:="", _
SubAddress:=Worksheets(sheetCount).Name & "!A1", _
TextToDisplay:=Worksheets(sheetCount).Name
Next
' 罫線を引く
Call SetLineStyle(sheet, rowNoOutputData)
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ヘッダーを設定する
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub SetHeader(ByVal sheet As Worksheet)
' ヘッダーを出力する
sheet.Cells(ROWNO_HEADER, COLNO_OUTPUT_NO).Value = "No"
sheet.Cells(ROWNO_HEADER, COLNO_OUTPUT_SHEETNAME).Value = "シート名"
' ヘッダーに色を付ける
sheet.Cells(ROWNO_HEADER, COLNO_OUTPUT_NO).Interior.Color = rgbLawnGreen
sheet.Cells(ROWNO_HEADER, COLNO_OUTPUT_SHEETNAME).Interior.Color = rgbLawnGreen
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 罫線を引く
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub SetLineStyle(ByVal sheet As Worksheet, ByVal endRowno As Long)
With sheet.Range(Cells(ROWNO_HEADER, COLNO_OUTPUT_NO).Address, Cells(endRowno, COLNO_OUTPUT_SHEETNAME)).Borders
.LineStyle = xlContinuous
End With
End Sub
目次には罫線を引き、目次のヘッダー部分は色を変更しています。
罫線の引き方、背景色の変え方は以下の記事にまとめてありますので、必要であれば合わせてご覧ください。


2. 準備
シートの隣にある+を押してシートを追加します。シート数は適当でOK。
3. マクロを実行する
画面上部の【▶】を押して、MakeMokujiSheetを選択し、実行ボタンを押します
1シート目(1番左)に、目次シートが追加されて、シート名が一覧で表示されていることを確認します。
また、リンクがきちんと作成されていることも確認しましょう。
B2セルにあるSheet1をクリックすると、Sheet1のA1セルに移動します。
これで完了です!
「目次に戻る」ハイパーリンクを作成するマクロ
今度は各シートから目次に戻るハイパーリンクを作成します。シート数が多いと戻るのが面倒なので目次を作るのと一緒にあると便利だと思い作成しました。
1. VBAのソースコード
メモ
- 目次を作るマクロで使用した変数SHEETNAME_MOKUJIをそのまま使用しています。
- ハイパーリンクを表示する位置を変更する場合は、ws.Range(“A1”)のA1を例えばB2などに変更すればOKです。
目次に戻るハイバーリンク作成
copy
' 目次へ戻るを作成する
Public Sub MakeReturnMokuji()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> SHEETNAME_MOKUJI Then
' ハイパーリンクを設定
ws.Hyperlinks.Add Anchor:= _
ws.Range("A1"), _
Address:="", _
SubAddress:=Worksheets(SHEETNAME_MOKUJI).Name & "!A1", _
TextToDisplay:=Worksheets(SHEETNAME_MOKUJI).Name & "へ戻る"
End If
Next ws
End Sub
手作業で作成してもいいのですが、シートが増えるたびに修正するのは面倒ですよね。VBAを書ければ、意外と簡単にマクロを作れます。
面倒くさい作業はどんどん自動化していきましょう!
↓ VBAを学びたい人におすすめの書籍です!