VBAで値リストが設定されるセルで値が選択された際に別のセルの値リストの内容を書き換える方法について覚書。
例えば大分類、小分類など2つのセルを関連付けて大分類で選択された値によって小分類に表示される値を絞り込んで値リストに設定したい場合に使った方法です。
やりたいことの概要
例えば、以下のような「商品分類」と「商品分類」に属する商品に関する表があったとします。
別のシートのB列には、「データの入力規則」にリスト表示で「商品分類」が設定されていて、ドロップダウンリストから商品分類が選択できるようになっています。
B列のセルで「商品分類」から何れかの分類名を選択したら、選択された「商品分類」に所属する「商品」だけが隣のC列のセルのドロップダウンリストに表示されるようにします。
設定方法
まず「商品」シートに「商品分類」の一覧の表、「商品分類」とその「商品分類」に所属する「商品」の一覧表を作成します。
以下はそのサンプル表です。
「商品入力」シートには、「商品分類」と「商品」を入力する以下のような表が作成されています。
データの入力は、セルB3からB12までで、B列「商品分類」の隣のC列は、商品を入力するセルになっています。
「商品入力」シートのB3からB12までのセルを選択し、リボンの[データ]タブを開いて[データの入力規則]コマンドをクリックします。
[データの入力規則]ダイアログボックスが表示されるので、[入力値の種類]に「リスト」を選択し、[元の値]に「商品分類」シートのA2からA5までを指定して[OK]をクリックします。
これで「商品入力」シートのB3からB12までのセルを選択するとドロップダウンリストから「商品分類」の値が表示されて選択できるようになります。
セルの値リストを更新するVBA
今回は「商品入力」シートのセルB3～B12の値が変更されたタイミングで隣のセルで選択できる値リストを更新する(書き換える)処理を実行する必要があるので、ワークシートのイベントプロシージャとして記載します。
ワークシートのイベントプロシージャは、標準モジュールを使う場合と異なるので、以下にイベントプロシージャへの記載方法を説明します。
リボンの[開発]タブをクリックし、[Visual Basic]コマンドをクリックします。
※[開発]タブがない場合は、リボンを右クリック、[リボンのユーザー設定]からメインタブの一覧の[開発]のチェックをオンにすれば表示されます。
「Microsoft Visual Basic for Applications」画面が表示されるので、左ペインから[商品入力]シートをダブルクリックします。
真っ白のエディタ画面が表示されるので、上部にある[(General)]と表示されているコンボボックスから[Worksheet]を選択します。
自動的に隣のコンボボックスに[SlectionChange]が選択され、エディタに[SlectionChange]イベントプロシージャが表示されます。
[SlectionChange]が選択されているコンボボックスから[Change]を選択します。
エディタに以下のような[Change]イベントプロシージャが表示されることを確認してください。
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
以降に紹介するVBAのコードは、この[Worksheet_Change]イベントプロシージャ内に記載します。
[Sub Worksheet_SelectionChange」のイベントプロシージャは、使わないので削除しても構いません。
サンプルプログラム
「商品分類」が選択されたタイミングで隣のセルの「商品」の値リストの設定を変更するサンプルプログラムを以下に記載します。
Private Sub Worksheet_Change(ByVal Target As Range)
If IsArray(Target.Value) Then Exit Sub
If (Target.Row >= 3 And Target.Row <= 12) And (Target.Column = 2) Then
'B列で選択された値によってC列のリストを作成する処理
i = 2
Do Until Worksheets("商品").Range("C" & i).Value = ""
If Worksheets("商品").Range("C" & i).Value = Target.Value Then
strList = strList & Worksheets("商品").Range("D" & i).Value & ","
End If
i = i + 1
Loop
With Selection.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=strList
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Target.Offset(0, 1).Value = ""
End If
End Sub
補足説明 No.1
[Worksheet_Change]イベントプロシージャは、セルの値に変更があった場合に実行されるイベントプロシージャです。
今回のサンプルでは、「商品入力」シートのB3～B12のセルで値が変更された場合に実行したいので、変更があったセルが実行させたいセルの範囲かを判定するためにプログラムの5行目にIF分を使っています。
Private Sub Worksheet_Change(ByVal Target As Range)
If IsArray(Target.Value) Then Exit Sub
If (Target.Row >= 3 And Target.Row <= 12) And (Target.Column = 2) Then
'B列で選択された値によってC列のリストを作成する処理
i = 2
Do Until Worksheets("商品").Range("C" & i).Value = ""
「Target」は値の変更があったセルを表すRangeオブジェクトなので「Target」の行番号が3行以上、12行目以下で、かつ、列番号が2(B列)の場合だけIf～End Ifに書かれた処理を実行するという意味になります。
つまりセル範囲で言えばB3～B12です。
補足説明 No.2
次にプログラムの7行目から13行目までのDo Until ～ LoopでB列で選択された「商品分類」に所属する「商品」を","区切り文字列結合してC列の値リストに設定する文字列を作る処理が動作します。
i = 2
Do Until Worksheets("商品").Range("C" & i).Value = ""
If Worksheets("商品").Range("C" & i).Value = Target.Value Then
strList = strList & Worksheets("商品").Range("D" & i).Value & ","
End If
i = i + 1
Loop
「商品分類」シートのC2セルの値が「商品入力」シートのB列で選択された値と等しいか比較し、等しい場合はその隣のD列の値を変数に代入します。
等しくなければそのまま何もせず、変数 i に1を足して一つ下の値に対して同じように処理をします。
比較するセルがループするごとに下に下がっていき、最終的にC列の値が何も入力されていない(「商品分類」シートのC14セル)まで来たらLoop処理から出ます。
文字列は、「商品入力」シートのB列で選択された値と等しい場合は、","と結合されて文字列が作成されていきます。
例えば、「商品入力」シートのB列で「やさい」が選択されたら、作成される文字列は、「トマト,レタス,きゅうり,」になります。
最後の","が余計ですが、別にそのままでも支障がないので特に削除する処理はしていません。
補足説明 No.3
プログラムの15行目～26行目で「商品入力」シートのC列の値リストを前処理で作成した文字列(strList変数)を値リストに設定する処理になります。
With Selection.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=strList
.IgnoreBlank = True '空白入力を許可
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
この処理はマクロ記録で記録されたコードから不必要なものを削除したコードなので、詳細な説明はしませんが、「Formula1:=」に作成したリスト文字列(strList変数)を代入することで、C列の値リストが変更されます。
補足説明 No.4
プログラムの27行目は、B列の値が別の値に変更された際にC列に前の値が残っているのはまずいので、C列の値を削除する処理になります。
End With
Target.Offset(0, 1).Value = ""
End If
End Sub
この処理を入れる場所を間違えると無限ループになるので注意してください。
プログラムからセルの値を削除した場合でもイベントプロシージャが動作します。
補足説明 No.5
プログラムの最初(3行目)でIsArray関数を使って引数Targetが配列かどうか判断している処理が記載されています。
Private Sub Worksheet_Change(ByVal Target As Range)
If IsArray(Target.Value) Then Exit Sub
If (Target.Row >= 3 And Target.Row <= 12) And (Target.Column = 2) Then
これはB列で複数選択した状態で削除などの処理が行われると実行時エラーとなるためです。
まとめ
セルで選択された値によって別のセルの値リストを変更するサンプルを記事にしました。
サンプルレベルなので動作的に多少の問題があることに理解してください。
例えば、B列で同じ分類を選び直す、つまりB列の値は変更されていないにもかかわらず、値が選び直されたことでイベントプロシージャが動作してC列の値がクリアされてしまう等です。
変更前の値を保管しておいて結果的に変更がなければその値を使うという方法がとれるかもしれませんが、そこまで試してません。
また、セルの操作については複数選択時のエラーしか考慮していないので、なにか別の操作をしたときに実行時エラーが発生するかもしれません。
プログラムの最初に「On Error Resume Next」を入れればすべてのエラーをパスできますが、なるべくなら実行時エラーが発生するケースごとに処理を記載して潰すのが正しいと思うので使いませんでした。
Accessなんかではよく使われる処理ですが、Excelでやろうとすると結構難しいですね。
コメント