【Excel VBA】シートの一覧(ハイパーリンク付き)を作るマクロ

Excel VBA シートの一覧(ハイパーリンクつき)のマクロ

全てのシートの一覧を作りたい、欲を言えば一覧にはハイパーリンクも付けたい …という要望があったのでマクロを作成しました!

シートの目次を作るマクロ(ハイパーリンク付き)

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

目次には罫線を引き、目次のヘッダー部分は色を変更しています。

罫線の引き方、背景色の変え方は以下の記事にまとめてありますので、必要であれば合わせてご覧ください。

Excel VBA 罫線の設定方法【Excel VBA】セルに罫線を設定する方法 Excel VBA セルの色を設定・取得する方法【Excel VBA】セルに色を付ける、セルの色を取得する方法

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を学びたい人におすすめの書籍です!