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

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)
        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
    
    '対象セルの値が商品名に含まれていたらListBox1のリストに追加する
    Dim i As Long
    Dim c As Long
    With ListBox1
        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
        .ListIndex = 0 '最初のリストを選択
    End With
End Sub

'+++ QueryCloseイベント +++
'フォームを閉じる際に発生
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Range(strTarget).ClearContents
    If CloseMode = vbFormControlMenu Then Range(strTarget).Offset(, -1).ClearContents
End Sub

'■リストボックスイベント(ListBox1)
'+++ Changeイベント +++
'リストボックスに変更があった場合に発生
Private Sub ListBox1_Change()
    Range(strTarget).Offset(, -1).Value = ListBox1.List(ListBox1.ListIndex, 1)
End Sub

'+++ KeyDownイベント +++
'リストボックスでキーを押した場合に発生
Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case vbKeyReturn
            Unload Me
        Case vbKeyEscape
            Range(strTarget).Offset(, -1).Value = ""
            Unload Me
    End Select
End Sub

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

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

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

■ 購入：amazon