Excel VBA シート名をリスト（プルダウン）に動的に設定するサンプル

Excelでシート名をリスト（プルダウン）で選択できるようになったらいいな、と思ってやってみました。

シートのセルをリスト入力にし、自分のシート名以外をリストの値に設定する記録です。

< スポンサーリンク >

シート名をリストの値に設定する

リスト入力させたいシートがアクティブになった時に、シート名を取得し、リスト入力できるように設定します。

［開発］タブ＞［コードの表示］をクリックします。

左側のオブジェクトボックスを「WorkSheet」を選択します。

右側のプロシージャボックスに「Activate」を選択します。

「Private Sub Worksheet_SelectionChange(ByVal Target As Range)　・・・　End Sub」は今回必要ないので削除します。

「Private Sub Worksheet_Activate()」と「End Sub」の間に下記のコードを書き込みます。

※リスト入力用のセルはB2に設定します。

Private Sub Worksheet_Activate()
    'このブック用
    Dim wbThis As Workbook
    'このシート用
    Dim wsThis As Worksheet
    'ループ用
    Dim ws As Worksheet
    'リスト入力用
    Dim ListStr As String
    
    '変数にこのブックをセット
    Set wbThis = ThisWorkbook
    '変数にこのシートをセット
    Set wsThis = wbThis.Worksheets("Sheet1")
    
    'リスト入力用変数に空文字を代入
    ListStr = ""
    
    'このシートと名前が違うシートをリストに追加
    For Each ws In wbThis.Worksheets
        If ws.Name <> wsThis.Name Then
            'リスト入力用変数が空ではない場合「,」を最後に追加
            If ListStr <> "" Then
                ListStr = ListStr & ","
            End If
            'リスト入力変数にファイル名を追加
            ListStr = ListStr & ws.Name
        End If
    Next ws
    
    'このシートのB2のセルにファイル名をリスト入力に設定
    With wsThis.Range("B2").Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=ListStr
    End With
End Sub