トップ > 逆引きで学ぶ ユーザーフォーム&コントロール > 追加章　現場で活かせる！実践ユーザーフォーム > B-04　キー入力にあわせて候補を絞り込むリストを作成

Option Explicit
'【定数】
Const TGT_RANGE As String = "B2:B11" '入力補助フォーム表示対象のセル範囲

'■シートイベント(Sheet1)
'+++ SelectionChangeイベント +++
'セルの選択が変更された場合に発生するイベント
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    '「入力補助」チェックボックスがOffの場合は抜ける
    If CBool(Range("E1")) = False Then Exit Sub

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

Option Explicit

Private varData   As Variant
Private lngSelect As Long

'■フォームイベント(UserForm)
'+++ Initializeイベント +++　インスタンス生成時に発生
Private Sub UserForm_Initialize()
    'フォームの表示位置設定
    Dim sinLeft  As Single
    Dim sinTop   As Single
    Dim dblX     As Double
    Dim dblY     As Double
    With ActiveSheet.Range(strTarget)
        sinLeft = .Offset(, 1).Left '対象セルの一つ右のセル水平位置取得
        sinTop = .Offset(, 1).Top   '対象セルの一つ右のセル垂直位置取得
    End With
    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 lngLastRow As Long
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        If lngLastRow < 3 Then lngLastRow = 3
        varData = .Range("A2:B" & lngLastRow).Value
    End With

    'テキストボックスにフォーカスを移す
    TextBox1.SetFocus
End Sub

'■テキストボックスイベント(TextBox1)
'+++ Changeイベント +++　値に変化があった時に発生
Private Sub TextBox1_Change()
    'リストボックスのリストを全てクリア
    ListBox1.Clear
    
    'テキストボックス1が空白の場合は抜ける
    Dim strText As String
    strText = TextBox1.Text
    If Len(strText) = 0 Then Exit Sub
    
    With ListBox1
        Dim i As Long
        '変数varDataにテキストボックスの値が含まれる場合は配列を追加する
        For i = 1 To UBound(varData)
            If InStr(1, CStr(varData(i, 1)), strText, vbTextCompare) Then
                'リスト（商品コード）を追加
                .AddItem varData(i, 1)
                '2列目に商品名を追加
                .List(.ListCount - 1, 1) = varData(i, 2)
            End If
        Next
        If .ListCount = 0 Then Exit Sub
        'リストを選択状態にする
        If .ListCount - 1 < lngSelect Then lngSelect = .ListCount - 1
        .ListIndex = lngSelect
    End With
End Sub

'+++ KeyDownイベント +++　キーが押された時に発生
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                             ByVal Shift As Integer)
    With ListBox1
        If .ListCount = 0 Then 'リストがない場合は抜ける
            '[Enter]か[Esc]が押されていたらフォームを閉じる
            If KeyCode = vbKeyReturn Or KeyCode = vbKeyEscape Then Unload Me
            Exit Sub
        End If
        Select Case KeyCode
            Case vbKeyReturn '[Enter]が押された
                '選択されている項目をセルに設定
                Range(strTarget).Value = .List(lngSelect)
                Unload Me
            Case vbKeyEscape '[Esc]が押された
                Unload Me
            Case vbKeyUp     '[↑]が押された
                lngSelect = lngSelect - 1
                If lngSelect < 0 Then lngSelect = 0
                .ListIndex = lngSelect
            Case vbKeyDown   '[↓]が押された
                lngSelect = lngSelect + 1
                If .ListCount - 1 < lngSelect Then lngSelect = .ListCount - 1
                .ListIndex = lngSelect
        End Select
    End With
End Sub

'+++ Exitイベント +++　フォーカスが抜ける時に発生
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Cancel = True 'テキストボックスからフォーカスが移るのをキャンセルする
End Sub

'■リストボックスイベント(ListBox1)
'+++ DblClickイベント +++　ダブルクリックされた時に発生
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    '選択されている項目をセルに設定
    Range(strTarget).Value = ListBox1.List(ListBox1.ListIndex)
    Unload Me
End Sub

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

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

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

■ 購入：amazon