VBAで別シートのリストを入力規則として設定する

入力規則を別シートで管理する

Excelのセルには事前に決められた値をプルダウンで選択できるようにする仕組みがあります。「データの入力規則」機能です。

設定されているセルにカーソルがあたると▼のプルダウン表示になります。

この設定をマクロで実施する方法を紹介します。

なお、Excel上での入力規則の操作方法ですが、Excelのデータタブ→データツール→「データの入力規則」を押すと、「データの入力規則」ダイアログが表示され、入力値の種類で「リスト」を選択して、元の値で別シートの列やセル範囲を選択することで設定できます。

「元の値」のセル範囲に左の絵の「$A:$A」のように書けばA列全体、右の絵の「$A$2:$A$5」のように書けばA2セルからA5セルの4セルが対象、という書き方になります。

ちなみに、プルダウンの元になるデータのシートですが、１行目は空にしておいて、２行目以降に選択肢を設定しておき、入力規則で「入力規則シート!$A:$A」のように列全体を指定すると、あとで選択肢が増えても設定を変更する必要がなくなりますのでお勧めです。欠点として、B列やC列に長いプルダウンの選択肢があると、A列のプルダウンも空白選択肢が増えてしまうという点があります。これが嫌な場合は「$A$2:$A$5」のようにセル範囲にしてください。

入力規則設定マクロ（単純バージョン）

以下のマクロは別シート（「入力規則」シート）に設定されている１種類の列を入力規則として設定するマクロです。

前提として、「入力規則」シートのA列に以下のようにデータが設定されているものとします。A列に、空、あり、なし、不明。

別のシートで入力規則を設定したいセル範囲を選択して以下のマクロを実行すると、「入力規則」シートのA列の内容がセルにプルダウンで選択できるようになります。

Sub SetInputRule()
    Dim sr  As Range        '// 選択セル範囲
    
    Set sr = Selection
    
    With sr.Validation
        '// 設定済みの入力規則を削除
        .Delete
        
        '// 入力規則を設定（「入力規則」シートのA列をプルダウン選択肢とする）
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=入力規則!$A:$A"
        
        '// セルへの空白入力をあり（True）
        .IgnoreBlank = True
        
        '// プルダウン表示あり（True）
        .InCellDropdown = True
    End With
End Sub

Sub SetInputRule()

Dim sr As Range '// 選択セル範囲

Set sr = Selection

With sr.Validation

'// 設定済みの入力規則を削除

.Delete

'// 入力規則を設定（「入力規則」シートのA列をプルダウン選択肢とする）

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=入力規則!$A:$A"

'// セルへの空白入力をあり（True）

.IgnoreBlank = True

'// プルダウン表示あり（True）

.InCellDropdown = True

End With

End Sub

実行すると、こんな感じになります。

複数列の入力規則を設定するマクロ

上のマクロは単純に入力規則を設定するものですが、複数の入力規則データがある場合も紹介します。

前提として、「入力規則」シートのA、B列に以下のようにデータが設定されているものとします。

A列に、空、あり、なし、不明。
B列に、0以上～10未満、10以上～20未満、20以上～30未満、30以上～40未満、40以上～50未満、50以上。

それぞれを、アクティブシートのG列とH列に入力規則のプルダウンとして表示できるようにするマクロです。

関数が２つありますが、実行するのは１つ目のSetInputRule2関数で、そこからSetInputRuleFunction関数を呼び出しています。入力規則を設定したいシートをアクティブにして実行します。２つ目の関数には引数が２つあり、アクティブシートのどこにプルダウンを設定するのかを指定するセル範囲のRangeオブジェクトと、入力規則が設定されているシートのどの部分を入力規則として適用するのかをValidation.AddメソッドのFormula1プロパティに設定する文字列として指定します。

Sub SetInputRule2()
    Dim sht As Worksheet    '// アクティブシート
    Dim sr  As Range        '// 選択セル範囲
    
    Set sht = ActiveSheet
    
    '// アクティブシートのG列に、入力規則シートのA列で入力規則を設定する
    Set sr = sht.Range("G:G")
    Call SetInputRuleFunction(sr, "=入力規則!$A$1:$A$4")
    
    '// アクティブシートのH列に、入力規則シートのB列で入力規則を設定する
    Set sr = sht.Range("H:H")
    Call SetInputRuleFunction(sr, "=入力規則!$B$1:$B$7")
End Sub

'// 引数1：sr As Range      ：入力規則を設定するセル範囲（プルダウンを設定する側）
'// 引数2：sRule As String  ：入力規則の元データセル範囲
Sub SetInputRuleFunction(sr As Range, sRule As String)
    With sr.Validation
        '// 設定済みの入力規則を削除
        .Delete
        
        '// 入力規則を設定（「入力規則」シートのA列をプルダウン選択肢とする）
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=sRule
        
        '// セルへの空白入力をあり（True）
        .IgnoreBlank = True
        
        '// プルダウン表示あり（True）
        .InCellDropdown = True
    End With
End Sub

Sub SetInputRule2()

Dim sht As Worksheet '// アクティブシート

Dim sr As Range '// 選択セル範囲

Set sht = ActiveSheet

'// アクティブシートのG列に、入力規則シートのA列で入力規則を設定する

Set sr = sht.Range("G:G")

Call SetInputRuleFunction(sr, "=入力規則!$A$1:$A$4")

'// アクティブシートのH列に、入力規則シートのB列で入力規則を設定する

Set sr = sht.Range("H:H")

Call SetInputRuleFunction(sr, "=入力規則!$B$1:$B$7")

End Sub

'// 引数1：sr As Range ：入力規則を設定するセル範囲（プルダウンを設定する側）

'// 引数2：sRule As String ：入力規則の元データセル範囲

Sub SetInputRuleFunction(sr As Range, sRule As String)

With sr.Validation

'// 設定済みの入力規則を削除

.Delete

'// 入力規則を設定（「入力規則」シートのA列をプルダウン選択肢とする）

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=sRule

'// セルへの空白入力をあり（True）

.IgnoreBlank = True

'// プルダウン表示あり（True）

.InCellDropdown = True

End With

End Sub

入力規則を別シートで管理する

入力規則設定マクロ（単純バージョン）

複数列の入力規則を設定するマクロ

関連記事