入力規則のリストを簡単に設定する｜Excel VBA

「入力規則のリスト」といえば、便利な機能ながら、設定に手間がかかる操作の代表格ですね。ここでは、入力規則のリストを簡単に設定するマクロを考えてみましょう。

まず、入力規則のリストを設定する操作をマクロ記録してみます。

Sub Macro1()
　　With Selection.Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
　　　　xlBetween, Formula1:="東京,横浜,神戸"
　　　　.IgnoreBlank = True
　　　　.InCellDropdown = True
　　　　.InputTitle = ""
　　　　.ErrorTitle = ""
　　　　.InputMessage = ""
　　　　.ErrorMessage = ""
　　　　.IMEMode = xlIMEModeNoControl
　　　　.ShowInput = True
　　　　.ShowError = True
　　End With
End Sub

ここから不要な部分を削除すると次のようになります。

Sub Macro1()
　　With Selection.Validation
　　　　.Add Type:=xlValidateList, _
　　　　　　Formula1:="東京,横浜,神戸"
　　End With
End Sub

リストに登録する"東京,横浜,神戸"は、アクティブセルに入力しておくことにすると、

Sub Macro1()
　　With Selection.Validation
　　　　.Add Type:=xlValidateList, _
　　　　　　Formula1:=ActiveCell.Value
　　End With
End Sub

これで完成です。

ですが、リストに表示するデータを半角のカンマで区切るのは少々面倒です。
特に日本語入力がオンになっているときは、いったん日本語入力をオフにして半角のカンマを入力しなければなりません。
そこで、"東京 横浜 神戸" や "東京　横浜　神戸" のようにスペースで区切られたデータがアクティブセルに入力されていた場合は、そのデータを入力規則のリストに登録するようにしてみましょう。この方が使い勝手がいいですね。

アクティブセルに入力されたデータに半角カンマが含まれていた場合はそのままリストのデータとして採用します。半角カンマが含まれていなかった場合は「スペース区切り」とみなしてスペースをカンマに置換します。

Sub Macro1()
　　Dim ListData As String
　　ListData = ActiveCell.Value
　　If InStr(ListData, ",") = 0 Then
　　　　ListData = Replace(ListData, "　", " ")
　　　　ListData = Replace(ListData, " ", ",")
　　End If
　　With Selection.Validation
　　　　.Add Type:=xlValidateList, _
　　　　　　Formula1:=ListData
　　End With
　　ActiveCell.ClearContents
End Sub

Replace関数を2回実行しているのは、最初に全角のスペースを半角のスペースに置換しているからです。最後にアクティブセルをクリアするようにしました。

これで、アクティブセルにリスト項目を入力してマクロを実行すれば選択範囲に入力規則のリストがワンタッチで登録できるようになりました。
マクロをボタンに登録したり、ショートカットキーを割り当てておくと便利ですね。