Excelで複数のリストボックスを連動させるには：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、選択されたリストボックスの内容に応じて、もう一つのリストボックスの内容を変える処理を中心に複数のリストボックスを連動させる方法を解説します。

[薬師寺国安，PROJECT KySS]

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013

　今回もリストボックスコントロールの使い方について解説する。複数のリストボックスを連動する方法を紹介しよう。

　複数のリストボックスを連動する方法は、使い方によっては大変に便利なTipsではないだろうか。社員の「氏名」を表示させておいてクリックすると、その社員の詳細データが表示されるといった使い方も可能だ。

2つのリストボックスを連動するには

　まず、Excelシートに、図1のようなデータと、「フォームを表示」ボタンを作成しておく。

図1　データと「フォームを表示」ボタンが配置

　次に、今まで解説した手順でユーザーフォームを作成する。UserForm1のCaptionプロパティに、「リストボックスの連動フォーム」と指定しておく。

　ユーザーフォーム上には、リストボックスを2個配置しておく。プロパティから、最初のリストボックスの「オブジェクト名」には、「タイトルリストボックス」、次のリストボックスの「オブジェクト名」には、「内容リストボックス」としておく。

　ここでは図2のように配置しリストボックスの「書体」や「文字サイズ」は、「Font」プロパティから指定しているが、読者の皆さんの好きな指定にしてもらって構わない。

図2　ユーザーフォームに、リストボックスを2個配置した

　プロジェクト内のUserForm1を選択して、マウスの右クリックで表示される、「コードの表示」を選択してコードを記述する。

ユーザーフォームがアクティブになったときの処理

　まず、ユーザーフォームがアクティブになったとき、「タイトルリストボックス」にデータを追加するコードはリスト1だ。UserForm_Activateイベント内に記述する。

Option Explicit
Private Sub UserForm_Activate()
  With タイトルリストボックス
    .AddItem Range("B2").Value
    .AddItem Range("C2").Value
  End With
End Sub

リスト1　「タイトルリストボックス」にデータを表示するコード

　「B3」のセルと「C2」のセルデータ（タイトル）をAddItemで「タイトルリストボックス」に追加する。

選択されたリストボックスの内容に応じて、もう一つのリストボックスの内容を変える処理

　次に、「タイトルリストボックス」から選択された「タイトル」に、該当する内容を「内容リストボックス」に表示するコードはリスト2になる。タイトルリストボックス_Changeイベント内に記述する。

Private Sub タイトルリストボックス_Change()
  内容リストボックス.Clear
  Select Case タイトルリストボックス.Text
    Case "フォームTips"
      Call 背景色を白にする
      Range("B2").Interior.ColorIndex = 6
      内容リストボックス.List = Range("B3:B8").Value
    Case "VBA関数"
      Call 背景色を白にする
      Range("C2").Interior.ColorIndex = 6
      内容リストボックス.List = Range("C3:C11").Value
  End Select
End Sub

リスト2　選択された「タイトルリストボックス」内の、「タイトル」に該当するデータを「内容リストボックス」に表示するコード

　まず2行目で「内容リストボックス」を一度クリアしておく。こうしておかないと、「タイトル」を選択するごとに、内容が重複して追加されるので、注意が必要だ。

　3〜13行では「タイトルリストボックス」から選択した「タイトル」で、条件分岐を行う。どの条件になっても「背景色を白にする」プロシージャを呼んでいる（5・9行目）。これについてはリスト4で後述する。

　「タイトル」が「フォームTips」であった場合は、「B2」セルの背景色を「6（黄）」で塗りつぶす（6行目）。これは、「内容リストボックス」内に表示された内容を選択した際、選択した内容の項目の背景色を「3（赤）」に変化させているが、「タイトル」の部分まで「3（赤）」で塗りつぶされるため、背景色を「6（黄）」で再度塗りつぶしているのだ。

　7行目では「内容リストボックス」に、Listプロパティで「B3」のセルから「B8」のセルまでのデータを表示している。

　「タイトル」が「VBA関数」であった場合も、「タイトル」を再度「6（黄）」で塗りつぶし（10行目）、「内容リストボックス」に、Listプロパティで「C3」のセルから「C11」のセルまでのデータを表示している（11行目）。

二つ目のリストボックスの値が選択されたときの処理

　「内容リストボックス」の値が選択されたときのコードはリスト3になる。内容リストボックス_Changeイベント内に記述する。

Private Sub 内容リストボックス_Change()
  Select Case タイトルリストボックス.Text
    Case "フォームTips"
      Call 背景色を白にする
      Cells(内容リストボックス.ListIndex + 3, 2).Interior.ColorIndex = 3
    Case "VBA関数"
      Call 背景色を白にする
      Cells(内容リストボックス.ListIndex + 3, 3).Interior.ColorIndex = 3
  End Select
End Sub

リスト3　「内容リストボックス」の値が選択されたとき、Excel上の同じデータの背景色が「赤」に変化するコード

　リスト2と同様、リスト3でも「背景色を白にする」プロシージャを呼び出す（4・7行目）。これについてもリスト4に後述する。

　5行目では「内容リストボックス」の選択されたインデックスに、「3」を加算した行と、「2列目（B列）」の背景色を「3（赤）」で塗りつぶす。「3」を加算しているのは、リストボックスの選択されたインデックスは「0」から始まるため、「3行目」から開始するために「3」を加算している。

　「タイトル」が「VBA関数」であった場合も同じで、今度は「3列目（C列）」の背景色を「3（赤）」で塗りつぶす（8行目）。

背景色を白で塗りつぶす

　次は「背景色を白にする」プロシージャの処理だ（リスト4）。

Sub 背景色を白にする()
  Dim i As Integer
  For i = 3 To 8
    Cells(i, 2).Interior.ColorIndex = 2
  Next
  
  For i = 3 To 11
    Cells(i, 3).Interior.ColorIndex = 2
  Next
End Sub

リスト4　背景色を白で塗りつぶすコード

　まずInteger型の変数「i」を宣言し（2行目）、変数「i」を「3」から「8」まで繰り返す（3〜5行目）。この数値は「行番号」に該当する。「i」行目の「2列目（B列）」の背景色を一度全部「2（白）」で塗りつぶしておく（4行目）。

　次に、変数「i」を「3」から「11」まで繰り返す（7〜9行目）。この数値は「行番号」に該当する。「i」行目の「3列目（C列）」の背景色を一度全部「2（白）」で塗りつぶしておく（8行目）。

　この処理をしていないと、「内容」を選択した場合、Excelシートの該当するデータが、全て「3（赤）」で塗りつぶされることになって都合が悪い。

フォームを表示する処理

　次にVBE（Visual Basic Editor）のメニューから、［挿入］→［標準モジュール］を選択する。プロジェクト内にModule1が追加されるので、この中に「フォームの表示」のコードを記述する（リスト5）。

Option Explicit
Sub フォームの表示()
  UserForm1.Show vbModeless
End Sub

リスト5　フォームを表示するコード

　ShowメソッドでUserForm1をモードレスで表示する簡単なものだ。

実行結果

　このリスト5のコードを図1の「フォームを表示」ボタンに関連付け、実行した結果が図3だ。

図3

　「タイトルリストボックス」の値を選択すると、対応する内容が「内容リストボックス」に表示され、内容を選択するとExcelシートの値の背景色が「3（赤）」になる。

連載目次

※本Tipsの環境：Windows 8.1 Enterprise（64ビット）＋Excel 2013

著者プロフィール

薬師寺 国安（やくしじ くにやす）　／　薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev （Oct 2003-Sep 2012）。

Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。

Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。

Microsoft MVP for Development Platforms-Windows Platform Development (Oct 2014-Sep 2015）。