【エクセルマクロ】入力規則のリスト内容を取得：3選

エクセルマクロで、入力規則のリスト内容を取得するVBAコードをご紹介します。

このページのマクロコードは、コピペで使えます。
ぜひお試しください！

入力規則のリスト内容を出力

以下のExcelVBAを実行すると、入力規則のリスト項目を転記します。

Sub 入力規則ドロップダウンリスト取得()

Dim i As Long

    'A列　最終行までループ
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    
        With Cells(i, 1).Validation

            If .Type = xlValidateList Then

                'B列にリスト出力
                Cells(i, 2) = .Formula1
            
            End If
        End With
    Next i

End Sub

マクロ実行前

A列に入力規則（リスト）を設定しています。

セル範囲 [A2：A4]　　→ a,b,c,d,e,f
セル範囲 [A5：A7]　　→ あ,い,う,え,お
セル範囲 [A8：A10]　 → りんご,いちご,めろん

マクロ実行後

A列に設定している入力規則の「元の値」を、B列に出力します。

入力規則のリスト内容を出力（参照先・名前定義）

以下のExcelVBAを実行すると、入力規則の元の値を取得します。

Sub 入力規則の参照元を書き出す()

Dim i As Long

    'A列　最終行までループ
    For i = 2 To 10
    
        With Cells(i, 1).Validation
        
            '入力規則がリストだったら
            If .Type = xlValidateList Then
            
                'B列に「元の値」を転記
                Cells(i, 2) = Mid(Cells(i, 1).Validation.Formula1, 2)
            
            End If
        End With
    Next i

End Sub

マクロ実行前

セル範囲 [A2：A5] は、別シートをセル参照してリストを設定しています。

セル範囲 [A6：A10] は、名前定義からリストを設定しています。

マクロ実行後

A列に設定している入力規則の「元の値」を、B列に出力します。

入力規則のリスト内容を出力（参照先のセル値）

以下のExcelVBAを実行すると、入力規則の参照先の文字を取得します。

Sub 入力規則リスト内容取得()

Dim i As Long
Dim j As Long
Dim GetList As Variant
Dim Buf As Variant
Dim Str1 As String
Dim Str2 As String
Dim GetSht As String
Dim GetCell As String

    'A列　最終行までループ
    For i = 2 To 10

        With Cells(i, 1).Validation

            '入力規則がリストだったら
            If .Type = xlValidateList Then

                '参照元のシート名を取得　「Sheet2」
                If InStr(.Formula1, "!") >= 1 Then
                    Str1 = Left(.Formula1, InStr(.Formula1, "!") - 1)
                    Str2 = Replace(Str1, "=", "")
                    GetSht = Str2
                    
                    '参照元のセルを取得　「C1:C6」
                    Str1 = Right(.Formula1, InStr(.Formula1, "!"))
                    Str2 = Replace(Str1, "$", "")
                    GetCell = Str2
                Else
                    GetSht = ActiveSheet.Name
                    Str2 = Replace(Replace(.Formula1, "$", ""), "=", "")
                    GetCell = Str2
                End If
                
                '参照元のセル値を取得　「Sheet2　C1:C6」
                Buf = Sheets(GetSht).Range(GetCell)

                '参照元のセル値をカンマ区切りで取得
                For j = 1 To UBound(Buf)
                    GetList = GetList & Buf(j, 1) & ","
                Next j
                
                '取得した値を転記
                Cells(i, 2) = GetList
                
                '初期化
                Erase Buf
                GetList = ""

            End If
        End With
    Next i

End Sub

マクロ実行前

A列に入力規則（リスト）を設定しています。

セル範囲 [A2：A5] → =Sheet2!$C$1:$C$6
セル範囲 [A6：A10] → =Sheet2!$A$1:$A$6

「元の値」の参照先のシート

マクロ実行後

A列に設定している入力規則の「元の値」の参照先から値を取得して、B列に出力します。

 
この記事がお役に立ちますと幸いです。
 
・【エクセルマクロ】入力規則を自動処理する方法まとめ
 

【エクセルマクロ】入力規則を自動操作する：超便利5選

...

kirinote.com

2022.05.28