Excel:入力規則で選択された値によって他のセルの入力規則リストを変更する方法

Excel

入力規則の設定されているセルで選択された値によって、別のセルの入力規則のリストを変更する方法をVBAを使用してを作成したので覚書しておく。

Accessのフォームなどではよく使う機能で、1つ目のリストで選択された値を基に2つ目のリストに表示するリストを1つ目のリストで選択された値に関係する値だけ抽出して表示する、いわゆる親子関係を持たせることをセルの入力規則を使って実現した。

Excelには入力規則という機能があって、あらかじめセルに入力規則を設定しておくと、セルを選択した際にドロップダウンリストボックスが表示され、設定したリストから選択して入力できる。

セルに入力される値があらかじめある程度の種類に決まっている場合、入力を簡略化したり、入力間違いなどを防ぐ意味でも役に立つ機能である。

以下サンプルの説明。

サンプルファイルの構成と動作について

サンプルファイルの構成と動作について簡単に説明する。

Sheet1には、以下のような表が作成されており、商品区分と商品名、単価、数量、合計金額が列として用意されている。

サンプル表

また、”マスター”シートには、商品区分列に設定する入力規則のリストと商品名列に設定する入力規則の基となるリストが用意されている。

商品名リストには、商品がどの商品区分属しているかが記述されている。

マスターリスト

商品区分列のセルを選択すると自動的にセルの入力規則のリストに”マスター”シートの商品区分リストの内容が設定される。

商品区分の選択

商品区分のリストから値を選択すると、選択された商品区分に属する商品の一覧が”マスター”シートのリストを基に商品名セルの入力規則のリストに自動的に設定される。

商品名の選択

商品名を選択すると、単価列に選択した商品の単価が”マスター”シートのリストを基に自動的に入力される。

単価が自動的に入力される

数量列に数値を入力すると合計額列に”数量 X 単価”の値が自動的に入力される。

合計額が自動的に計算されて入力される

商品区分で上のセルとは違う区分を選んだ場合に表示される商品名リストの例。

商品区分で選択され値よって商品名リストが変わる

今回事情があってExcelでこういった処理ができるように作成したが、本来こういったものはAccessのフォームで作るのが普通だと思う。

また、Excelの入力規則のリストに設定できる文字列数に制限があるため、あまり多くのリストを設定することはできない。(確か255文字)

もしExcelでこのようなことをするのであれば、そういった制限等よく考えた上で作成すること。

各処理に必要なイベントプロシージャ(VBAのコード)について

上記の動作に必要な各イベントプロシージャについて説明する。

セルの値が変更された際に実行されるイベントプロシージャ

以下は、Sheet1でセルの値が変更された際に動作するイベントプロシージャ。

セルが選択された際に実行されるイベントプロシージャ

このプロシージャでは、商品区分列のセルが選択された際に商品区分列のセルに表示するドロップダウンリストのリストの初期化と商品区分列で選択された区分によって商品名列のセルに表示するドロップダウンリストのリストの初期化処理を行っている。

サンプルExcelファイルのダウンロード

説明だけでは分かりづらいと思うのでサンプルのExcelファイルを用意してダウンロードできるようにした。

サンプルExcelファイルのダウンロード:sample.xls

サンプルファイルの使用については自己責任でお願いします。

ファイルを開いた際にVBAのコードエラーが出る場合があるようですが、無視して構いません。 確認した限りサンプルとしての使用について問題はありません。

よく読まれている記事のようなので、記事を見やすく改版しました。