Excel VBAのリストボックスの使い方をお探しですね。
広告
Excel VBAのリストボックス・コンボボックスでセルの値を使う方法
Excel VBAのユーザーフォームで入力画面を作るとき、リストボックスやコンボボックスにセルの値を表示して、選んだ内容を別のセルに書き込む場面ってよくありますよね。
手入力を減らせるので、商品名や担当者名、分類、コードなんかの入力ミスを防ぐのにすごく便利です。
この記事では、セル範囲から値を追加する基本から、選んだ値の取得方法、複数列のリスト、更新時の注意点まで、実務で使いやすい形で説明していきます。
リストボックスとコンボボックスって何ができるの?
Excel VBAのユーザーフォームに配置するリストボックスとコンボボックスは、どちらも「候補の中から値を選んでもらう」ためのパーツです。
リストボックスは候補がずっと一覧で表示されるので、選択肢を見比べながら選びたい画面に向いています。
一方、コンボボックスは普段は1行だけ表示されていて、クリックすると候補がパッと開くタイプなので、画面をすっきり見せたいときに便利です。
どちらもセルに入力されている値を候補として読み込んで、選ばれた値をセルに書き込むことができます。
ちょっとややこしいのが、ワークシート上に配置するフォームコントロールやActiveXコントロールと、VBAのユーザーフォーム上に配置するリストボックス・コンボボックスは扱い方が少し違うという点です。
ワークシート上のフォームコントロールでは「入力範囲」や「リンクするセル」を設定して使うことが多いんですが、ユーザーフォームではVBAコードで`.AddItem`や`.List`、`.RowSource`といった命令を使って候補を設定します。
この記事では、ユーザーフォーム上のコントロールを中心に、セルの値を追加して選んだ値を取得する方法を整理していきますね。
まず覚えておきたいのは、リストの作り方には大きく3つあるということです。
– **少ない固定値なら** → `.AddItem`で1件ずつ追加する方法が分かりやすい
– **セル範囲をまとめて読み込みたいなら** → `.List`を使うと効率的
– **セル範囲と常に連動させたいなら** → `.RowSource`を設定する方法もある
ただし、`.RowSource`を使っている状態で`.AddItem`や`.Clear`を混ぜて使うとエラーになったり、思わぬ動きをしたりするので、どの方式でリストを作るかを最初に決めておくことが大切です。
AddItemでセルの値を1件ずつ追加する基本
一番分かりやすい方法は、ユーザーフォームを開いたタイミングでセルを順番に読み取って、リストボックスやコンボボックスに`.AddItem`で追加していく方法です。
ユーザーフォームには`UserForm_Initialize`というイベントがあって、フォームが表示される前に自動で実行されます。
この中にリスト作成の処理を書いておけば、フォームを開いた時点でセルの値が候補として表示されるようになります。
たとえば、Sheet1のA2からA10に担当者名が入力されている場合、こんな感じで書けます。
“`vb
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Worksheets(“Sheet1”)
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
With ComboBox1
.Clear
For i = 2 To lastRow
If ws.Cells(i, “A”).Value <> “” Then
.AddItem ws.Cells(i, “A”).Value
End If
Next i
End With
With ListBox1
.Clear
For i = 2 To lastRow
If ws.Cells(i, “A”).Value <> “” Then
.AddItem ws.Cells(i, “A”).Value
End If
Next i
End With
End Sub
“`
このコードでは、A列の最終行を自動で取得してから、A2以降の空白じゃないセルだけを候補に追加しています。
`For i = 2 To 10`みたいに固定で書くこともできるんですが、実務ではリストの件数が増えたり減ったりすることが多いので、最終行を自動取得する形にしておくとメンテナンスが楽になります。
`.Clear`は既存の候補を全部消す命令で、フォームを開き直したりリストを更新したりするときに、同じ候補が何度も追加されちゃうのを防ぐ役割があります。
`.AddItem`はシンプルで初心者にも扱いやすいんですが、数千件以上のデータを1件ずつ追加すると処理が遅くなることがあります。
また、複数列のデータを表示したい場合は、列数の指定や各列への値の代入が必要になって、コードがちょっと長くなります。
少ない候補や、条件を判定しながら1件ずつ追加したい場合には`.AddItem`が向いていますが、セル範囲をそのまま一覧にしたい場合は、次に紹介する`.List`や`.RowSource`も検討してみてください。
ListやRowSourceでセル範囲をまとめて読み込む
セル範囲をまとめてリストボックスやコンボボックスに表示したいときは、`.List`プロパティにセル範囲の値を配列として渡す方法が便利です。
Excelのセル範囲を`.Value`で取得すると、行と列を持つ2次元配列として扱えるので、複数列のリストも比較的簡単に作れます。
たとえば、Sheet1のA2:C10に「商品コード」「商品名」「価格」が並んでいる場合、リストボックスに3列表示するコードはこんな感じです。
“`vb
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets(“Sheet1”)
lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row
With ListBox1
.Clear
.ColumnCount = 3
.ColumnWidths = “60;120;60”
.List = ws.Range(“A2:C” & lastRow).Value
End With
With ComboBox1
.Clear
.ColumnCount = 3
.ColumnWidths = “60;120;60”
.List = ws.Range(“A2:C” & lastRow).Value
End With
End Sub
“`
`.ColumnCount`は表示する列数、`.ColumnWidths`は列幅を指定するプロパティです。
列幅はポイント単位で、セミコロン(`;`)で区切って指定します。
リストボックスでは複数列を見せる使い方が多くて、商品コードと商品名を同時に表示したいときなんかに便利です。
コンボボックスでも複数列表示はできるんですが、選択後に表示される値は設定によって見え方が変わるので、何をユーザーに見せたいのかを考えて列の構成を決める必要があります。
もう1つの方法として、`.RowSource`にセル範囲のアドレスを指定するやり方もあります。
これはセル範囲と連動しやすくて、VBEのプロパティ画面から設定することもできます。
コードで設定する場合はこんな感じです。
“`vb
Private Sub UserForm_Initialize()
ComboBox1.RowSource = “‘Sheet1’!A2:A10”
ListBox1.RowSource = “‘Sheet1’!A2:A10”
End Sub
“`
`.RowSource`は簡単なんですが、注意点があります。
`.RowSource`を設定しているコントロールに対して`.AddItem`で追加したり、`.Clear`で削除したりするとエラーになることがあるんです。
リストをVBAで自由に作り替えたい場合は、`.RowSource`じゃなくて`.AddItem`か`.List`を使う方が安全です。
逆に、セル範囲をそのまま表示して、セル側の変更を候補に反映させたいだけなら`.RowSource`は手軽です。
切り替える場合は、先に`ComboBox1.RowSource = “”`みたいに空文字に戻してから、`.Clear`や`.AddItem`を使うとトラブルを避けやすくなります。
選んだ値を取得してセルに書き込む実用パターン
リストボックスやコンボボックスにセルの値を追加できたら、次は選ばれた値を取得してシートに書き込む番です。
コンボボックスの場合、選択中の値は基本的に`.Value`で取得できます。
`.Text`でも表示中の文字列を取得できるんですが、入力可能なコンボボックスでは編集中の文字列を含むことがあるので、普通の選択値として扱うなら`.Value`を使う方が分かりやすいです。
ボタンをクリックしたときに選択値をSheet1のE2に書き込むなら、こんな感じです。
“`vb
Private Sub CommandButton1_Click()
If ComboBox1.ListIndex = -1 Then
MsgBox “コンボボックスから値を選択してください。
“, vbExclamation
Exit Sub
End If
ThisWorkbook.Worksheets(“Sheet1”).Range(“E2”).Value = ComboBox1.Value
End Sub
“`
`ListIndex`は、選択されている行番号を表すプロパティです。
何も選択されていない場合は`-1`になります。
先に`ListIndex = -1`をチェックしておくと、未選択のまま登録されちゃうミスを防げます。
リストボックスでも単一選択なら同じ考え方で、`ListBox1.Value`や`ListBox1.List(ListBox1.ListIndex, 0)`を使って選択値を取得できます。
複数列のリストから2列目の商品名や3列目の価格を取り出したい場合は、列番号を指定して取得します。
“`vb
Private Sub CommandButton2_Click()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“Sheet1”)
If ListBox1.ListIndex = -1 Then
MsgBox “リストボックスから行を選択してください。
“, vbExclamation
Exit Sub
End If
ws.Range(“E2”).Value = ListBox1.List(ListBox1.ListIndex, 0) ‘1列目
ws.Range(“F2”).Value = ListBox1.List(ListBox1.ListIndex, 1) ‘2列目
ws.Range(“G2”).Value = ListBox1.List(ListBox1.ListIndex, 2) ‘3列目
End Sub
“`
VBAのリストボックスやコンボボックスでは、行番号と列番号が**0から始まる**ので注意してください。
つまり、1行目は`0`、1列目も`0`です。
この点はExcelのセル番地と感覚が違うので、最初はちょっとつまずきやすいポイントです。
たとえば`ListBox1.List(ListBox1.ListIndex, 1)`は、選択行の**2列目**を意味します。
商品コードを1列目、商品名を2列目、価格を3列目にしている場合、どの列を書き込みたいのかを明確にしておくと、登録処理のミスを減らせます。
選んだ瞬間にセルに反映させたい場合は、ボタンじゃなくて`Change`イベントを使います。
ただし、フォーム初期化中にリストを設定しただけでも`Change`イベントが発生することがあるので、複雑なフォームでは初期化中かどうかを判定するフラグを用意すると安全です。
簡単な例なら、こんな感じで選択変更時にセルに書き込めます。
“`vb
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex <> -1 Then
ThisWorkbook.Worksheets(“Sheet1”).Range(“E2”).Value = ComboBox1.Value
End If
End Sub
“`
まとめ:実務で使いやすい構成にするコツ
実務で使う場合は、データの追加、選択、書き込み、更新の流れを分けて考えると安定します。
– **候補を作る処理** → `UserForm_Initialize`または専用の更新プロシージャにまとめる
– **登録ボタン** → 選択値のチェックとセルへの書き込みだけを行う
こんな風に構成を分けておくと、後から修正するときも楽になります。
使い分けのポイントとしては、
– **セル範囲が頻繁に変わる** → `.List`で読み直す方法
– **常に同じ範囲を参照する** → `.RowSource`
– **条件に応じて候補を絞り込む** → `.AddItem`
という感じです。
Excel VBAのユーザーフォームは、うまく使えば入力支援ツールとしてすごく便利になるので、ぜひ試してみてくださいね!
広告
