[[20201016102553]]　『コンボボックスのサジェスト機能』（じゃす）　ページの最後に飛ぶ

『コンボボックスのサジェスト機能』（じゃす）

こんにちは。
今回コンボボックスのプルダウンリストをサジェスト機能のように該当するもののみ表示できればいいなと思い下記コードを書いてみました。
　Private Sub ComboBox8_Change()

    Dim n As Long, m As Long, i As Long
    Dim X As String
    Dim Z As Worksheet
    Dim A As Variant
    Set Z = Worksheets("LIST2")'このシートのH列3行目からリストインしたいデータがある
    n = ComboBox8.ListCount
    If n = 0 Then
    Exit Sub
    End If
    m = Z.Cells(Rows.count, "H").End(xlUp).Row
    A = Z.Range(Z.Cells(3, "H"), Z.Cells(m, "H")).Value'デフォルトのリストと同様の範囲(事前に追加済み)
    X = ComboBox8.Text
    ComboBox8.List = Array()
    For i = 1 To m - 1
    If Mid(X, 1, Len(X)) = Mid(A(i, 1), 1, Len(X)) Then
    ComboBox8.AddItem A(i, 1)
    End If
    Next i
    ComboBox8.DropDown
　End Sub
何とか形になったかなと思ったのですが、コンボボックスに1文字目を入力しただけなのにオートコンプリートのようにリスト内の1番初めに該当する数値が入力されてしまうせいで、入力した文字がリスト内の先頭から一致しているリストを表示したいと思ってもうまく検索がかけられませんでした。

それを踏まえてkeypressで下記コードを書いてみました。

 Private Sub ComboBox8_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim n As Long, m As Long, i As Long
    Dim X As String
    Dim Z As Worksheet
    Dim A As Variant
    Set Z = Worksheets("LIST2")
    n = ComboBox8.ListCount - 1
    If n = -1 Then
    Exit Sub
    End If
    m = Z.Cells(Rows.count, "H").End(xlUp).Row
    A = Z.Range(Z.Cells(3, "H"), Z.Cells(m + 1, "H")).Value
    X = Chr(KeyAscii)
    ComboBox8.List = Array()
    For i = 1 To m - 1
    If Mid(X, 1, Len(X)) = Mid(A(i, 1), 1, Len(X)) Then
    ComboBox8.AddItem A(i, 1)
    End If
    Next i
    ComboBox8.DropDown
 End Sub

こっちは1文字目は期待通りの動作をしてくれるようには持っていけたのですが、2文字目以降の判定をどのようにすればよいかわからなかったため質問を立てさせていただきます。

コンボボックスの予測入力機能？をoffにする方法
ならびに
keypressイベントで2文字目以降を認識し、事前に入力した内容と合わせサジェストする方法

このような方法があればお教えいただきたく思います。

よろしくお願いします。

＜使用 Excel：Excel2007、使用 OS：WindowsXP ＞

 テストして試そうにも、どういうデータが入ってるのかわからん
 テストできるデータを提供してもらえませんか？
（稲葉） 2020/10/16(金) 12:41

興味があったので書いてみました。
新規でユーザーフォームとコンボボックス１つ、
以下コードをユーザーフォームに貼り付けてください。

データはシート名Sheet1のA1からA列に入れてください。

何となくは動いています。
参考にどうぞ。

 Option Explicit
 Private BeforStr As String
 Private Sub UserForm_Initialize()
     ComboBox1.MatchEntry = fmMatchEntryNone
 End Sub
 Private Sub ComboBox1_Change()
     Dim c As New Collection
     BeforStr = ComboBox1.Value

     If BeforStr <> "" Then Set c = FindProcess(BeforStr)

     Dim listArr() As String
     Dim cnt As Long, v As Variant
     If c.Count = 0 Then
         ComboBox1.Clear
     Else
         ReDim listArr(1 To c.Count)
         For Each v In c
             cnt = cnt + 1
             listArr(cnt) = v
         Next v
         ComboBox1.List = listArr
     End If
     ComboBox1.Visible = False
     ComboBox1.Visible = True
     ComboBox1.SetFocus
     ComboBox1.DropDown
 End Sub
 Private Function FindProcess(ByVal findStr As String) As Collection
     Dim findArr As Variant
     With Worksheets("Sheet1")
         findArr = .Range("A1", .Cells(Rows.Count, "A").End(xlUp)).Value
     End With

     Dim c As New Collection
     Dim v As Variant
     For Each v In findArr
         If InStr(v, findStr) = 1 Then c.Add v
     Next v
     Set FindProcess = c
 End Function

（tkit） 2020/10/16(金) 14:13

返信遅くなりすみません。
稲葉さん　1～100までの数値をリストとして入力していました。将来的には漢字カナ記号数値入り混じった文章をリストとして使用できればと考えています。
tkitさん　貴重なサンプルコードありがとうございます。matchentryの影響で予測入力が行われていました。
サンプルをもとに下記のように書き直すことでいい感じに動くようにはなったのですが、リスト内容の更新を行った後ドロップダウンリストを表示した際、リストの表示数が1になってしましました。
.List=で追加したときは適応したListRowsが反映されているのでAddItemの仕様なのか？と疑っています。
とりあえず動的配列に放り込んで事なきを得たのですが、上記事象に関してお答えいただければと思います。
よろしくお願いします。

　Private Sub ComboBox4_Change()
　Dim X As Worksheet
　Dim n As Long, m As Long, p As Long
　Dim A As Variant
　Dim B As String
　Set X = Worksheets("LIST2")
　n = X.Cells(Rows.count, "E").End(xlUp).Row
　A = X.Range(X.Cells(3, "E"), X.Cells(n + 1, "E")).Value
　B = ComboBox4.Value
　If B = "" Then
　　ComboBox4.List = A
　Else
　　ComboBox4.List = Array()
　For m = 1 To n - 1
　　If Left(A(m, 1), Len(B)) = B Then
　　　ComboBox4.AddItem A(m, 1)
　　Else
　　　p = p + 1
　　End If
　Next m
　End If
　If p = n - 1 Then
　　ComboBox4.List = A
　End If
　ComboBox4.ListRows = 10
　ComboBox4.DropDown
　End Sub

（じゃす） 2020/10/22(木) 13:28

＞上記事象に関してお答えいただければと思います。

ステップ実行しながら、ローカルウィンドウで
変数やプロパティを見ていけば分かると思いますよ。
（tkit） 2020/10/22(木) 16:34

tkitさん
ありがとうございます。
納得のいく答えを得ることができました。
（じゃす） 2020/10/26(月) 12:19

シート上に基になるリストがあるなら、
入力に合わせ、フィルターオプションで前方一致で絞り込んだものを、
反映させればいいのでは？
（まっつわん） 2020/10/26(月) 18:05

皆さんご指導ありがとうございました。
こんな感じで完成系にしましたので一応ご報告まで。

  Private Sub ComboBox4_Change() 'ComboBox4の値が変わったとき
  Dim A As Variant, C As Variant 
  Dim B As String
  Dim D() As String 
  Dim n As Long, m As Long, p As Long, r As Long 
  Dim Z As Worksheet 
  Set Z = Worksheets("LIST2") 'ZをLIST2のシートにする
  n = Z.Cells(Rows.count, "E").End(xlUp).Row 'nをZのE列最終行とする
  A = Z.Range(Z.Cells(3, "E"), Z.Cells(n + 1, "E")).Value 'AをZの(E3)から(En+1)の値とする
  B = ComboBox4.Value 'BをComboBox4の値とする
    If B = "" Then 'Bが""ならば
    ComboBox4.List = A 'ComboBox4のリストをAにする
    Else 'B = ""でないならば
    ComboBox4.List = Array() 'ComboBox4のリストを空の配列にする
    m = Len(B) 'mをBの文字列の長さとする
        For Each C In A 'CをAのそれぞれの値としてすべて処理する
            If Left(C, m) = B Then 'Cの左からm文字とBが等しいならば
            ReDim Preserve D(p) 'Dの要素数をpにする
            D(p) = C 'D(p)をCとする
            p = p + 1 'pをp+1にする
            End If 'If Left(C, m) = B Then
        Next 'For Eachを繰り返す
    End If 'If B = "" Then
    If p = 0 Then 'pが0ならば
    ComboBox4.List = A 'ComboBox4のリストをAにする
    Else 'p = 0でないならば
    ComboBox4.List = D 'ComboBox4のリストをDにする
    End If 'If p = 0 Then
  ComboBox4.DropDown 'ComboBox4のドロップダウンリストを表示する
  End Sub

（よん） 2020/10/27(火) 11:37

じゃす = よんどういうこと?
よんは [[20201026122736]] 11:43 『ForEachとForNextの使い方』
の投稿の人?
回答者に迷惑かかるのでニックネームは統一せよ。

（kl） 2020/10/27(火) 11:51

コメント返信：

[ 一覧(最新更新順) ]

[[20201016102553]] 『コンボボックスのサジェスト機能』（じゃす） ページの最後に飛ぶ

[[20201016102553]]　『コンボボックスのサジェスト機能』（じゃす）　ページの最後に飛ぶ