トップ > 逆引きで学ぶ ユーザーフォーム&コントロール > 追加章　現場で活かせる！実践ユーザーフォーム > B-01　セルの入力値に応じた候補リストを作成・表示(ComboBox)

Option Explicit
'【定数】
Const TGT_RANGE As String = "C2:C11" '検索欄表示対象のセル範囲

'■シートイベント(Sheet1)
'+++ Changeイベント +++
'セルが変更された場合に発生するイベント
Private Sub Worksheet_Change(ByVal Target As Range)

    '変更したセルが検索欄表示対象セル範囲外の場合は抜ける
    If Intersect(Range(TGT_RANGE), Target) Is Nothing Then Exit Sub
    
    '変更したセルが複数の場合は抜ける
    If 1 < Target.Cells.Count Then Exit Sub
    
    '変更したセルの値が空の場合は抜ける
    If Len(Target.Value) = 0 Then Exit Sub
    
    '変更したセルのアドレスをグローバル変数に格納
    strTarget = Target.Address
    
    'フォームを表示
    UserForm1.Show
End Sub

Option Explicit

'■フォームイベント(UserForm)
'+++ Initializeイベント +++
Private Sub UserForm_Initialize()
    Dim strValue As String
    Dim sinLeft  As Single
    Dim sinTop   As Single
    With ActiveSheet.Range(strTarget) '変数strTargetから対象セルを指定
        strValue = .Value             '対象セルの値を取得
        sinLeft = .Offset(, 1).Left   '対象セルの一つ右のセル水平位置取得
        sinTop = .Offset(, 1).Top     '対象セルの一つ右のセル垂直位置取得
    End With
    
    '検索ウィンドウ（フォーム）の表示位置設定
    Dim dblX As Double
    Dim dblY As Double
    With ActiveWindow
        dblX = .PointsToScreenPixelsX(0) / 96 * 72 + sinLeft * .Zoom / 100
        dblY = .PointsToScreenPixelsY(0) / 96 * 72 + sinTop * .Zoom / 100
    End With
    With Me
        .StartUpPosition = 0
        .Left = dblX
        .Top = dblY - 28 '-28：フォーム垂直位置調整（見出しの高さ分）
    End With
    
    '「商品データ」シートから商品コードと商品名を取得し変数に格納
    With Sheet2
        Dim varList    As Variant
        Dim lngLastRow As Long
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lngLastRow = 2 Then lngLastRow = 3
        varList = .Range("A2:B" & lngLastRow).Value
    End With
    
    '対象セルの値が商品名に含まれていたらComboBox1のリストに追加する
    Dim i As Long
    Dim c As Long
    With ComboBox1
        For i = 1 To UBound(varList)
            If 0 < InStr(1, varList(i, 2), strValue, vbTextCompare) Then
                .AddItem varList(i, 2), c   'リスト1列目に商品名を追加
                .List(c, 1) = varList(i, 1) 'リスト2列目に商品コードを追加
                c = c + 1
            End If
        Next
    End With
End Sub

'■コンボボックスイベント(ComboBox1)
'+++ Changeイベント +++
'コンボボックスに変更があった場合に発生
Private Sub ComboBox1_Change()
    With ComboBox1
        '商品を選択した場合、対象セルの一つ左のセルに商品コードを入力
        Range(strTarget).Offset(, -1).Value = .List(.ListIndex, 1)
    End With
End Sub

'■コマンドボタンイベント(CommandButton1)
'+++ Clickイベント +++
Private Sub CommandButton1_Click()
    Unload Me
End Sub

Option Explicit
'+++ 宣言セクション +++
'グローバル変数
Public strTarget As String

書籍紹介140以上のサンプルファイル付き！

知りたいがすぐわかる！　やりたいがすぐできる！
「Excel VBA 逆引きで学ぶ ユーザーフォーム&コントロール」（Kindle版）
ユーザーフォームを扱えると、VBAでできることが大きく広がります！
本書では、知りたいこと、やりたいことから、逆引きで学びを深められます。

■ 購入：amazon