Excelで入力ミスを防ぐために有効な機能のひとつが「ドロップダウンリスト」です。
入力内容をあらかじめリストで指定しておくことで、手入力のミスを防ぎ、データの統一性を保つことができます。
ただし、リストの内容が頻繁に変わる業務では、手動でリスト範囲を変更するのは手間です。
そんなときに役立つのが、VBAでドロップダウンリストの元データを自動で更新する仕組みです。
リストの内容をVBAで動的に更新する方法
次のマクロは、別シートにあるリストを参照し、指定のセル範囲に対してドロップダウンリストを設定し直す処理です。
Sub UpdateDropDownList()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim lastRow As Long
Dim rngList As Range
Dim rngTarget As Range
Set wsSrc = Worksheets("リスト") ' リスト元のシート
Set wsDst = Worksheets("入力シート") ' ドロップダウンを設定するシート
' リスト元の最終行を取得
lastRow = wsSrc.Cells(Rows.Count, 1).End(xlUp).Row
Set rngList = wsSrc.Range("A2:A" & lastRow) ' A列にリストがある想定
' ドロップダウンを設定したいセル範囲を指定
Set rngTarget = wsDst.Range("B2:B100")
' データバリデーションを一括で設定
With rngTarget.Validation
.Delete ' 既存のバリデーションを削除
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="='" & wsSrc.Name & "'!" & rngList.Address
End With
MsgBox "ドロップダウンリストを更新しました", vbInformation
End Subコードのポイント
rngListには、元となるリスト範囲を設定します。シート名＋アドレスを文字列で参照します。Validation.Deleteを使うことで、既存のドロップダウンを一旦削除してから設定しています。Formula1にシート名とアドレスを組み合わせた参照を使うのがコツです。
オプションを追加して使いやすくする
より丁寧な設定を行いたい場合は、次のようにオプション設定を加えることもできます。
With rngTarget.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="='" & wsSrc.Name & "'!" & rngList.Address
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With各プロパティの意味は次の通りです。
.IgnoreBlank = True：リスト内に空白があっても許容.InCellDropdown = True：セル右側に▼のマークを表示.ShowInput = True：セル選択時に入力メッセージを表示（設定していれば）.ShowError = True：リスト以外を入力した場合にエラーメッセージを表示（設定していれば）
実装するメリット
このようなドロップダウンリスト更新マクロを導入することで、次のようなメリットがあります。
- 入力用のドロップダウンが常に最新状態になる
- リストの更新漏れによる入力ミスを防げる
- 手動でのバリデーション設定が不要になり、設定ミスも減らせる
- 入力範囲やリスト内容の変更に柔軟に対応できる
- リストを他のシートに置いて見た目をすっきり保てる
特に、商品名・担当者名・部門名などの選択肢が頻繁に変わる業務で効果を発揮します。
まとめ
VBAを使えば、Excelのドロップダウンリストを外部リストから自動更新することができます。
- 別シートの最新データを自動で参照
- 入力セル範囲に一括でドロップダウンを設定
- リストが変わってもボタンひとつで更新可能
業務で入力リストが頻繁に変わる場合や、手間を省いて精度を上げたい場合にとても有効な手法です。
手動での設定に限界を感じている方は、ぜひこのマクロを取り入れてみてください。
