【エクセルマクロ】入力規則のリスト内容を取得:簡単時短

ExcelVBA_入力規則リスト内容取得-アイキャッチ エクセルマクロで効率化する

エクセルマクロで、入力規則のリスト内容を取得する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列に出力します。

 
この記事がお役に立ちますと幸いです。
 

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