入力規則のリストを簡単に設定する|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回実行しているのは、最初に全角のスペースを半角のスペースに置換しているからです。最後にアクティブセルをクリアするようにしました。
これで、アクティブセルにリスト項目を入力してマクロを実行すれば選択範囲に入力規則のリストがワンタッチで登録できるようになりました。
マクロをボタンに登録したり、ショートカットキーを割り当てておくと便利ですね。