【Excel VBA】UserFormのComboBoxにシートの値を一覧表示する方法｜AddItem・List・RowSource
みなさんこんにちは。
Excel VBAでUserFormを作成していると、ComboBoxにシート上の値を一覧表示して、ユーザーに選択してもらいたい場面も多いかと思います。
ComboBoxへ値を設定する方法には、AddItem、List、RowSourceなど複数の方法があります。
どの方法でも一覧を表示することはできますが、固定値を設定したいのか、シートの値をそのまま表示したいのか、重複を除外したいのかによって使い分けると、コードも分かりやすくなります。
そこで今回は、Excel VBAのUserFormに配置したComboBoxへ値を一覧表示する方法について、AddItem・List・RowSourceの違いも含めてご紹介したいと思います。
この記事はこんな人におすすめ
- UserFormのComboBoxに値を一覧表示したい方
- シートに入力されている値をComboBoxへ表示したい方
- AddItem・List・RowSourceの違いを知りたい方
- ComboBoxの重複除外や初期値の設定方法を知りたい方
今回作成するUserForm
今回は、シートに登録されている部署名をUserFormのComboBoxへ一覧表示する例で説明します。
UserFormにはComboBoxを1つ配置し、オブジェクト名は初期状態のComboBox1を使用します。
また、Sheet1のA列には次のような部署名が入力されているものとします。
A1：部署名
A2：営業部
A3：総務部
A4：システム部
A5：人事部
A6：営業部
AddItemで固定値を追加する方法
最も分かりやすい方法が、AddItemを使用してComboBoxへ1件ずつ値を追加する方法です。
表示する内容があらかじめ決まっている場合や、項目数が少ない場合には扱いやすい方法となります。
Private Sub UserForm_Initialize()
' ComboBoxにすでに登録されている値をすべて削除
' UserFormを再表示した際に同じ値が重複して追加されることを防ぐ
Me.ComboBox1.Clear
' AddItemを使用してComboBoxへ値を1件ずつ追加
Me.ComboBox1.AddItem "営業部"
Me.ComboBox1.AddItem "総務部"
Me.ComboBox1.AddItem "システム部"
Me.ComboBox1.AddItem "人事部"
End Sub
UserForm_InitializeはUserFormの初期化時に実行されるイベントです。
そのため、UserFormを表示したタイミングでComboBoxの一覧を自動的に作成することができます。
シートの値をAddItemで一覧表示する方法
次に、Sheet1のA列に入力されている値を最終行まで取得し、ComboBoxへ追加する方法です。
Private Sub UserForm_Initialize()
' ComboBoxへ表示するデータが存在するシートを格納する変数
Dim ws As Worksheet
' A列の最終行を格納する変数
Dim lastRow As Long
' ループ処理で使用する行番号を格納する変数
Dim i As Long
' ComboBoxへ表示する値が登録されているシートを指定
Set ws = ThisWorkbook.Worksheets("Sheet1")
' A列の最終行を取得
' A列の一番下から上方向へ検索して最後に値が入力されている行を取得
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' ComboBoxにすでに登録されている値をすべて削除
Me.ComboBox1.Clear
' 1行目は見出しのため、2行目から最終行まで繰り返し処理
For i = 2 To lastRow
' セルが空白ではない場合のみComboBoxへ追加
If ws.Cells(i, "A").Value <> "" Then
' A列の値をComboBoxへ1件ずつ追加
Me.ComboBox1.AddItem ws.Cells(i, "A").Value
End If
Next i
End Sub
この方法であれば、シートのデータ件数が増減しても最終行を自動取得して一覧へ追加することができます。
また、ループ中に条件を追加できるため、「空白を除外する」「特定の値だけ表示する」といった処理も組み込みやすいのが特徴です。
Listプロパティでセル範囲をまとめて設定する方法
シートのセル範囲をそのままComboBoxへ設定したい場合は、Listプロパティを使用する方法も便利です。
AddItemのように1件ずつ追加する必要がなく、セル範囲の値をまとめて設定することができます。
Private Sub UserForm_Initialize()
' ComboBoxへ表示するデータが存在するシートを格納する変数
Dim ws As Worksheet
' A列の最終行を格納する変数
Dim lastRow As Long
' ComboBoxへ表示する値が登録されているシートを指定
Set ws = ThisWorkbook.Worksheets("Sheet1")
' A列の最終行を取得
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' シートのA2から最終行までの値をComboBoxへまとめて設定
' Range.Valueで取得した2次元配列をListプロパティへ設定
Me.ComboBox1.List = ws.Range("A2:A" & lastRow).Value
End Sub
配列の値をListプロパティで設定する方法
ComboBoxへ表示する値をVBA側で加工してから設定したい場合は、配列を作成してListへ設定する方法もあります。
Private Sub UserForm_Initialize()
' ComboBoxへ設定する2次元配列を宣言
' Listプロパティへまとめて設定しやすいように行と列を持つ配列を使用
Dim itemList(0 To 3, 0 To 0) As Variant
' 配列へComboBoxに表示する値を設定
itemList(0, 0) = "営業部"
itemList(1, 0) = "総務部"
itemList(2, 0) = "システム部"
itemList(3, 0) = "人事部"
' 作成した配列をComboBoxへまとめて設定
Me.ComboBox1.List = itemList
End Sub
Microsoft FormsのListプロパティは行・列を持つ一覧を扱うことができるため、複数件のデータをまとめてComboBoxへ設定したい場合に便利です。
RowSourceでシート範囲を直接指定する方法
RowSourceを使用すると、ComboBoxの一覧データとしてワークシートのセル範囲を直接指定することができます。
Private Sub UserForm_Initialize()
' ComboBoxへ表示するデータが存在するシートを格納する変数
Dim ws As Worksheet
' A列の最終行を格納する変数
Dim lastRow As Long
' ComboBoxへ表示する値が登録されているシートを指定
Set ws = ThisWorkbook.Worksheets("Sheet1")
' A列の最終行を取得
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' RowSourceにSheet1のA2から最終行までを指定
' ComboBoxの一覧をワークシート上のセル範囲と直接関連付ける
Me.ComboBox1.RowSource = "'" & ws.Name & "'!A2:A" & lastRow
End Sub
RowSourceはコードを短く書くことができますが、ComboBoxの一覧がシート範囲と直接関連付けられます。
そのため、値を加工したり、重複を除外したり、条件によって表示内容を変更したい場合は、AddItemやListを使用したほうが扱いやすいかと思います。
また、RowSourceでデータを関連付けた状態ではAddItemによる項目追加と組み合わせず、どちらか一方の方法を使用するようにしてください。
重複した値を除外してComboBoxへ表示する方法
実際の業務では、シート上に同じ値が複数登録されていることもあります。
例えば今回のサンプルでは「営業部」が2件登録されています。
重複した値を1件だけ表示したい場合は、Scripting.Dictionaryを使用すると簡単です。
Private Sub UserForm_Initialize()
' ComboBoxへ表示するデータが存在するシートを格納する変数
Dim ws As Worksheet
' 重複チェックに使用するDictionaryオブジェクトを格納する変数
Dim dict As Object
' A列の最終行を格納する変数
Dim lastRow As Long
' ループ処理で使用する行番号を格納する変数
Dim i As Long
' シートから取得した部署名を格納する変数
Dim departmentName As String
' ComboBoxへ表示する値が登録されているシートを指定
Set ws = ThisWorkbook.Worksheets("Sheet1")
' Dictionaryオブジェクトを作成
' 参照設定を追加しなくても使用できるようにCreateObjectを使用
Set dict = CreateObject("Scripting.Dictionary")
' A列の最終行を取得
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' ComboBoxにすでに登録されている値をすべて削除
Me.ComboBox1.Clear
' 2行目から最終行まで繰り返し処理
For i = 2 To lastRow
' A列に入力されている部署名を取得
departmentName = Trim(CStr(ws.Cells(i, "A").Value))
' 空白ではない場合のみ処理
If departmentName <> "" Then
' Dictionaryに同じ部署名がまだ登録されていない場合のみ処理
If Not dict.Exists(departmentName) Then
' Dictionaryへ部署名を登録
' 以降、同じ部署名が出てきた場合に重複と判定できる
dict.Add departmentName, True
' 重複していない部署名だけをComboBoxへ追加
Me.ComboBox1.AddItem departmentName
End If
End If
Next i
End Sub
ComboBoxの初期値を設定する方法
UserFormを表示した直後からComboBoxの先頭項目を選択状態にしたい場合は、ListIndexを使用します。
' ComboBoxに1件以上の項目が登録されているか確認
If Me.ComboBox1.ListCount > 0 Then
' ListIndexは0から始まるため、
' 0を指定すると一覧の先頭項目を初期選択する
Me.ComboBox1.ListIndex = 0
End If
ListIndexは0から始まるため、0を指定すると最初の項目、1を指定すると2番目の項目が選択されます。
AddItem・List・RowSourceはどれを使えばよい？
どの方法を使用するか迷った場合は、次のように使い分けると分かりやすいかと思います。
| 方法 | おすすめの用途 |
|---|---|
| AddItem | 固定値、条件付き追加、重複除外など1件ずつ制御したい場合 |
| List | セル範囲や配列など複数件をまとめて設定したい場合 |
| RowSource | ワークシートの範囲をそのままComboBoxへ関連付けたい場合 |
特に実務では、単純な一覧表示であればList、条件による絞り込みや重複除外が必要であればAddItemを使用する場面が多いかと思います。
まとめ
いかがでしたでしょうか。
UserFormのComboBoxへ値を設定する方法は1つではなく、用途によってAddItem・List・RowSourceを使い分けることができます。
- 固定値や条件付きで1件ずつ追加する場合はAddItem
- セル範囲や配列をまとめて設定する場合はList
- シート範囲を直接関連付ける場合はRowSource
- 重複除外などの加工が必要な場合はAddItemとDictionaryを組み合わせる
- 初期値を選択する場合はListIndexを使用する
ComboBoxはUserFormを使用したツールでは頻繁に使用するコントロールとなりますので、今回ご紹介した方法を用途に応じて使い分けてみてください。
少しでもお役立ていただければ幸いです。
開発依頼について
ココナラでWebスクレイピング開発サービスを出品しております。
自分で開発をしようと思ったけど、VBAでのネット記事が少なく困っている方も多いかと思います。
そんな方はいつでもお気軽にご相談ください。
また、本ブログからご依頼いただいた方については割引特典がございますので、
ご不明点と合わせてメッセージをいただけると幸いです。
Excelにてブラウザ操作自動化ツールを作成します その作業、webスクレイピングを使って自動化しましょう！Webスクレイピング以外にもWebアプリの開発サービスも出品しております。
こちらについても、本ブログからご依頼いただいた方については割引特典がございますので、
お気軽にご相談ください。
Excelにてブラウザ操作自動化ツールを作成します その作業、webスクレイピングを使って自動化しましょう！
