[[20120719152740]]　『ユーザーフォームで複数の検索結果を表示したい』（とん）　ページの最後に飛ぶ

『ユーザーフォームで複数の検索結果を表示したい』（とん）

EXCEL2003です。お力を貸してください。

　　A行　　B行
　　10　　山田孝
　　15　　佐藤義和
　 120　　山田健一
　　・　　　・
　　・　　　・

上記の様にA行に番号、B行に氏名が入力されており（約300件）
ユーザーフォームのTEXTBOX1に山田と入力すると、

 10　山田孝
120　山田健一

という様に、山田を含む検索結果を違うBOXで、番号と氏名を
表示させるにはどうしたらいいでしょう？

そして可能であれば、検索結果の120をクリックすると、エクセルのSHEET1の
セルD1にクリックした120が入るようにしたいです。

どなたかよろしくお願いしますm(__)m

 ユーザーフォームにTextBox1とListBox1を配置。
 ListBox1のColumnCountはプロパティ設定で 2 にしておいてね。
 TextBox1に山田といったものを入力してEnterまたはTabをおすと（ほかのコントロールをクリックしてもいい）
 対象のものを表示。それを選ぶとD1に転記。

 Private Sub ListBox1_Click()
    Sheets("Sheet1").Range("D1").Value = ListBox1.Value
 End Sub

 Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim v() As Variant
    Dim c As Range
    Dim k As Long

    ListBox1.Clear
    With Sheets("Sheet1")
        With .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
            ReDim v(1 To 2, 1 To .Rows.Count)
            For Each c In .Cells
                If c.Offset(, 1).Value Like TextBox1.Value & "*" Then
                    k = k + 1
                    v(1, k) = c.Value
                    v(2, k) = c.Offset(, 1).Value
                End If
            Next
            If k = 0 Then
                MsgBox "指定の値は存在しません"
            Else
                ReDim Preserve v(1 To 2, 1 To k)
                ListBox1.List = WorksheetFunction.Transpose(v)
            End If
        End With
    End With
 End Sub

 (ぶらっと)

ぶらっとさん、ご回答ありがとうございます。
やりたい事が出来ました！感動です！

●ただ検索結果が一人の場合、
TEXTBOX1に佐藤を入力すると

15
佐藤義和

と2行になります。これでもOKですが、1行で表示できますでしょうか？

●TEXTBOX1に山田を入力すると

10　　　　　　山田孝
120　　　　　山田健一

と空白がたくさんあるのですが、空白を少なくする事は可能でしょうか？

●ぶらっとさんのコードを貼り付けて使用したら、結果は完璧だったのですが
なぜこのコードで出来るのか、今度の勉強のために理解したいです。
初心者でお手数をお掛けして申し訳ございませんが、各コードの意味を
教えて頂けるととても嬉しいです。

 >ただ検索結果が一人の場合、 ・・・2行になります

 確かに！　ということなら、別の記述方法、AddItem を使ったほうがよかったかもしれないけど
 現行のList直接指定のコードを継続して、

 変数規定で
    Dim w() As Variant
 を追加したうえで

            If k = 0 Then
                MsgBox "指定の値は存在しません"
            Else
                ReDim Preserve v(1 To 2, 1 To k)
                If UBound(v, 2) > 1 Then
                    ListBox1.List = WorksheetFunction.Transpose(v)
                Else
                    ReDim w(1 To 1, 1 To 2)
                    w(1, 1) = v(1, 1)
                    w(1, 2) = v(2, 1)
                    ListBox1.List = w
                End If
            End If

 >空白がたくさんあるのですが、空白を少なくする事は可能でしょうか

 うん、できる。ListBox1のColumnWidths で、各列ごとの列幅をポイント数で指定可能。
 たとえば 20;100 。　ここで、20 という指定もできる。１列目だけを20ポイント、あとはデフォルト値。
 今回の場合、後者でいいね。20 は実際に表示してみて加減調整してね。

 追記） コードを説明するなら、無理やり対応したアップ済みのコードではなく以下のほうがわかりやすいと思うので
 TextBox1_BeforeUpdate を以下で置き換えよう。　説明は、それをベースに後程。

 Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim c As Range

    ListBox1.Clear
    With Sheets("Sheet1")
        With .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
            ReDim v(1 To 2, 1 To .Rows.Count)
            For Each c In .Cells
                If c.Offset(, 1).Value Like TextBox1.Value & "*" Then
                    ListBox1.AddItem c.Value
                    ListBox1.List(ListBox1.ListCount - 1, 1) = c.Offset(, 1).Value
                End If
            Next
            If ListBox1.ListCount = 0 Then
                MsgBox "指定の値は存在しません"
            End If
        End With
    End With

 End Sub

 (ぶらっと)

ぶらっとさん　すごいです！ありがとうございますm(__)m

1行表示も出来ました。そしてColumnWidthsを20ptにすると願い通りの表示です。

後ひとつだけ質問させてくださいm(__)m

現在項目は2つリストに表示させていますが、もうひとつ追加して3つの項目を
リストに表示させるには、どこのコードを変更したらできますか？

番号　氏名　所属

ユーザーフォーム、使いこなせれたらとても便利ですね！頑張って勉強します（とん）

 >もうひとつ追加して3つの項目をリストに表示させるには、どこのコードを変更したらできますか

 それでは、あとでアップしたコードをベースに。（アップ後、１行、Redim コードを削除してある）

 まず、プロパティのColumnCount を 3　にしておく。

 で、ListBox1.List(ListBox1.ListCount - 1, 1) = c.Offset(, 1).Value　の下に
 ListBox1.List(ListBox1.ListCount - 1, 2) = c.Offset(, 2).Value を追加。

 もし、最初にアップしたコードの処理方法がお好みならそれに対応する変更をアップしてもいいけど？

 いずれにしても、コードの説明は後程。

 (ぶらっと)

 まず、リストボックスやコンボボックスのリストの正体は【配列】
 今回の場合は、●行２列の配列ということ。
 で、この配列の要素は 0 から始まっている。
 配列(2,1) これは、【3行目】の【2列目】を指す。ちょっと、ややこしいね。

 で、この配列の行数は ListCountプロパティで取得できる。これは、 1　からカウント。
 10 なら　この行数は 10行　ということ。

 もう１つ、リストで選択されたものがリスト内の何行目だったのかを ListIndexプロパティで
 取得できる。これは、 0 から始まっている。　3 なら 【4行目】が選択されたということ。
 選択されていない状態では、ListIndex は　-1 になっている。

 Private Sub ListBox1_Click()

 リストで選んだ瞬間にこのルーティンにとんでくる。
 この時、Valueプロパティには、選んだ行の、代表の列の値が入っている。
 代表の列は　ListBox1のBoundColumnで指定。省略時（初期値）は最初の列。
 なので、以下のコードで、Sheet1のD1に選んだ行の最初の列の値が転記されることになるね。

    Sheets("Sheet1").Range("D1").Value = ListBox1.Value

 Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

 BeforUpdateには、TextBox1にデータを【入れ終わった時】にとんでくる。
 よく、テキストボックスのChangeイベントを使う人がいるけど、Changeイベントは
 テキストボックスに１文字入力するたびに、発生するので、特殊な用途以外は
 使わないほうがいい。

    ListBox1.Clear

 　　　まず、前回抽出されていたリスト内容をクリア

    With Sheets("Sheet1")
        With .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
             For Each c In .Cells

 　　　これはシートの指定列からデータが存在するだけセルを１つずつ取り出す定番コード

   If c.Offset(, 1).Value Like TextBox1.Value & "*" Then

 　　　取り出したA列のセルの１つ右、つまり　B列の値を　TestBox1に入力した値に "*" をつけたもので【Like】判定。
　　　 Like についてはヘルプ等で調べてみてね。

  ListBox1.AddItem c.Value

  　　ListBox1 に行を追加し、その行の１列目に指定の値をいれる。

 ListBox1.List(ListBox1.ListCount - 1, 1) = c.Offset(, 1).Value

 　　 追加した行の2列目に値をセット。
　　　追加した行は、追加の結果の、このリストの行数が追加した行番号ということになるけど
　　　コメントしたようにリストの行番号、列番号は　0 からはじまるので、ListCount - 1 を行番号にしている。
　　　列は２列目なので　1 。

 (ぶらっと)

ぶらっとさん　再々の質問にも答えて頂き、更に詳しい説明まで本当にありがとうございましたm(__)m

LIKE演算子も調べてみました。
それによくCHAGEイベント使っていました（笑）
BeforeUpdateの方がいいですね！

ぶらっとさんの回答をじっくり勉強し、今後自分で応用が利かせるようにしていきます！

ぶらっとさん　そしてこのサイトに感謝です。
ありがとうございましたm(__)m

（とん）

コメント返信：

[ 一覧(最新更新順) ]

[[20120719152740]] 『ユーザーフォームで複数の検索結果を表示したい』（とん） ページの最後に飛ぶ

[[20120719152740]]　『ユーザーフォームで複数の検索結果を表示したい』（とん）　ページの最後に飛ぶ