【VBA】コンボボックスに選択肢を設定する方法(画像付き)連携して選択肢を変更する方法も解説

こんにちは!大阪市を拠点に活動している『縁紡ぐ』の稲垣です。

SWELLを使ったホームページ制作や、Excel、ACCESS、RPAなどのシステム開発を行っています。
また、Excel、Word、Outlookの研修や、情報セキュリティ研修も行っています。身近なITの相談相手になりたいと思っています。

お気軽にお問い合わせください。

【VBA】コンボボックスに選択肢を設定する方法(画像付き)連携して選択肢を変更する方法も解説

この記事では、コード上での設定方法やシートから取得して設定する方法を含め、さまざまなパターンを詳しく画像も付けて解説します。初心者の方でも簡単に理解できるように手順をひとつひとつ説明しますので、ぜひ最後までお読みください。

目次

コンボボックスを設置する方法

コンボボックスは、利用者がリストから選択肢を選ぶことができるUI要素です。ExcelのVBAでは、フォームコントロールやActiveXコントロールを使用してコンボボックスを作成できます。

ユーザーフォームオブジェクトにコンボボックスを設置する方法

ユーザーフォームの場合は、ツールボックスのコンボボックスのアイコンをクリックするとユーザーフォームに設置することができます。

シートにコンボボックスを設置する方法

シートにコンボボックスを設置する場合は、開発タブの挿入から、フォームコントロールとActiveXコントロールのどちらかから設定できます。

コード上で選択肢を設定する方法

VBAコードを使ってコンボボックスに選択肢を設定する方法を解説します。

まずは、シートにあるコンボボックスに設定する場合。

Sub SetComboBoxItems()
    
    'シートにあるコンボボックスに値を設定する場合
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 適切なシート名に変更
    
    ' フォームコントロールの場合
    With ws.DropDowns("ドロップ 1") ' 適切な名前に変更
        .RemoveAllItems
        .AddItem "選択肢1"
        .AddItem "選択肢2"
        .AddItem "選択肢3"
    End With
    
    ' ActiveXコントロールの場合
    With ws.OLEObjects("ComboBox1").Object ' 適切な名前に変更
        .Clear
        .AddItem "選択肢1"
        .AddItem "選択肢2"
        .AddItem "選択肢3"
    End With
    
End Sub

ユーザーフォームのコンボボックスに設定する場合。

Private Sub UserForm_Initialize()
  'ユーザーフォームの場合
    With Me.ComboBox1
        .Clear
        .AddItem "選択肢1"
        .AddItem "選択肢2"
        .AddItem "選択肢3"
    End With
End Sub

シートにある値を選択肢として設定する方法

シートから選択肢を取得してコンボボックスに設定する方法を解説します。

Sub SetComboBoxItemsFromSheet()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' シート名は適宜変更
    Set rng = ws.Range("A1:A3") ' シート名は適宜変更
    
    ' フォームコントロールの場合
    With ws.DropDowns("ドロップ 1") 
        .RemoveAllItems
        For Each cell In rng
            .AddItem cell.Value
        Next cell
    End With
    
    ' ActiveXコントロールの場合
    With ws.OLEObjects("ComboBox1").Object 
        .Clear
        For Each cell In rng
            .AddItem cell.Value
        Next cell
    End With
End Sub

動的に選択肢を変更する場合

Sub SetDynamicComboBoxItems()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 適切なシート名に変更
    
    ' 条件で選択肢の設定を変える
    If DatePart("w", Date) = vbSunday Then
        ' 日曜日の場合
        With ws.OLEObjects("ComboBox1").Object ' 適切な名前に変更
            .Clear
            .AddItem "休日"
            .AddItem "休日出勤"
        End With
    Else
        ' その他の日
        With ws.OLEObjects("ComboBox1").Object ' 適切な名前に変更
            .Clear
            .AddItem "出勤"
            .AddItem "有給休暇"
        End With
    End If
End Sub

連携して選択肢を変更するコンボボックス

2つのコンボボックスを連携します。例えばひとつ目のコンボボックスで都道府県を選択したら、2つ目のコンボボックスは、選んだ都道府県の市区町村が選択肢として表示される動きになります。

今回は、辞書(Dictionary)を使います。辞書は、同じキーの重複を許しません。そのため、キーがすでに登録がされているか調べることができるメソッドexistsを使って調べることができます。この機能を使ってコンボボックスに重複した選択肢がないように設定します。

データ

まずは、シートに都道府県と市区町村の表を作成します。

都道府県市区町村
北海道A
北海道B
北海道C
北海道H
青森県I
青森県J
青森県K
青森県L
岩手県M
岩手県N
岩手県O
岩手県P
宮城県Q
宮城県R
宮城県S
秋田県T
秋田県U
秋田県V

コンボボックスを2つ用意する

都道府県と市区町村を選択する2つのコンボボックスを用意します。

コードを作成

Private Sub Worksheet_Activate()
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim dict As Object
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 適切なシート名に変更
    Set rng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    Set dict = CreateObject("Scripting.Dictionary")
    
    For Each cell In rng
        ' 都道府県の重複がないか検査
        If Not dict.Exists(cell.value) Then
            ' ない場合は辞書に追加
            dict.Add cell.value, Nothing
        End If
    Next cell
    
    With ComboBox1
        ' 都道府県のコンボボックスの内容をクリアする
        .Clear
        ' 辞書からキーをひとつずつ取り出す
        For Each Key In dict.Keys
            ' コンボボックスに追加する
            .AddItem Key
        Next Key
    End With
End Sub

Private Sub ComboBox1_Change()
    '都道府県のコンボボックスに変更があったら呼び出される
    
    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 適切なシート名に変更
    Set rng = ws.Range("A2:B" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    ' 市町村のコンボボックスをクリア
    ComboBox2.Clear
    
    For Each cell In rng
        ' 選択された都道府県に対応する市町村を追加
        If cell.value = ComboBox1.value Then
            ComboBox2.AddItem cell.Offset(0, 1)
        End If
    Next cell
End Sub

連動したコンボボックスの完成

まとめ

今回は、VBAを使ってExcelのコンボボックスに選択肢を設定する方法を詳しく解説しました。コード上で設定する方法、シートから取得して設定する方法、連動して選択肢が変更するパターンについてもご紹介しました。これらの方法を活用して、使いやすいものを作成してみてください。

最後までお読みいただき、ありがとうございました。この記事が役に立ったと思ったら、ぜひシェアしてくださいね!

記事を書いた人

稲垣

  • Excel、ACCESSでのシステム開発が得意
  • ITスキルを共有し実践的に学びながら成長する人を見るのが幸せ
  • 自家焙煎するほどのコーヒー好き
  • 使用言語 VBA、Python、Javascript、Java、HTML、CSS etc.
  • 保有資格 Kintoneアソシエイト、日商簿記検定2級、マンション管理士、管理業務主任者、情報セキュリティマネジメント、ExcelVBA etc.
  • 業務フロー図の作成や業務時間分析を通して、効率化ポイントを探る人
  • お客様にとって本当に良いことかを第一に考える人
よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次