VBA コンボボックスで選択したキーワードで検索したい。

Question

度々お世話になっております。

やっと目標に近づけてきたのですが、またも問題が発生しました。

【やりたいこと】
・ユーザーフォームのコンボボックスでキーワードを選択。
・その選んだキーワードを別シート内(DATA)で抽出(Sheet2)したい。

イメージとしては、コンボボックスの選択欄を指定して変数に格納すれば、実現できるのではないかと考えているのですが、まず選択欄をどう指定したらよいのかもわかりません。

ネット上や本では選択項目をシートから抽出するやり方は見つけられたのですが、行き詰っています。

[sheet2] ボタン2を押すとユーザーフォームが出現する。

[ユーザーフォーム]

[DATA]

[code:Module1]

Sub Test()

    Dim X, Y As Long
    Dim HHP(6), ECO(6), AAA As String

    MT = "ぶどう"
    
    With ThisWorkbook.Worksheets("DATA")
    
        With .Cells.Find(waht:=MT)
            X = .Row
            Y = .Column
        End With
        
        Dim reHHP, reECO, reAAA As Long
            reHHP = X + 28
            reECO = X + 1
            reAAA = X + 56
        
        Dim i As Integer
        
        For i = 0 To 6
            HHP(i) = .Cells(reHHP + i, Y).Value
            ECO(i) = .Cells(reECO + i, Y).Value
        Next i
        
        
            AAA = .Cells(reAAA, Y).Value
            
        MsgBox "HHPの要素数:" & UBound(HHP) + 1 & vbLf & "HHPの中身:" & Join(HHP, ",")
        MsgBox "ECOの要素数:" & UBound(ECO) + 1 & vbLf & "ECOの中身:" & Join(ECO, ",")
        MsgBox AAA
        
    End With
    
    
    With ThisWorkbook.worksheeets("sheet2")
    
        Dim M As Integer
        
        For M = 0 To 6
            .Cells(M + 3, "C").Value = HHP(M)
            .Cells(M + 12, "C").Value = ECO(M)
        Next M
        
        Range("C21") = AAA
        
    End With

End Sub
    
'*****************************
'   ユーザーフォーム
'*****************************
    
Sub FormShow()
    
    UserForm1.Show vbModeless
    
End Sub

[UserForm1]

Option Explicit

Sub ユーザーフォームの表示()
  UserForm1.Show vbModeless
End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub CommandButton2_Click()

    Unload Me

End Sub

Private Sub UserForm_initialize()

Dim K As Integer

    For K = 3 To 10
        ComboBox1.AddItem Worksheets("DATA").Cells(2, K).Value
    Next
    

End Sub

Answer 1

現状で分からないのは、

イメージとしては、コンボボックスの選択欄を指定して変数に格納すれば、実現できるのではないかと考えているのですが、まず選択欄をどう指定したらよいのかもわかりません。

ですね。

Dim  MT As String

MT = UserForm1.ComboBox1.Value

コンボボックスの選択した値は、下記のコードでも同じ結果になりますが、上記の方がシンプルです。
MT = ComboBox1.List(ComboBox1.ListIndex)
ただしリストボックスの場合は、こちらの方法になります。

仕様が明確でないので提示のコードは精査してませんが、気になったことは、

    Dim X, Y As Long
    Dim HHP(6), ECO(6), AAA As String

これだと、X, HHP(6), ECO(6) はVariant型の変数になります。

    Dim X As Long, Y As Long
    Dim HHP(6) As String, ECO(6) As String, AAA As String

とすべきですね。

追記

フォームの設計をみると Searchボタンが配置してあるので、これをクリックしたときに検索したいということだと思われます。 なので、標準モジュール(Module1)ではなく、検索するコードはこのコマンドボタンのクリック時に記述すべきですね。
ということで、UserForm1 のモジュールは下記のような感じになります。

Option Explicit

'Searchボタン
Private Sub CommandButton1_Click()
    Dim X As Long, Y As Long
    Dim HHP(6) As String, ECO(6) As String, AAA As String
    Dim MT As String

    MT = ComboBox1.Value
    With ThisWorkbook.Worksheets("DATA")
        With .Range("C2:J2").Find(waht:=MT)
            X = .Row
            Y = .Column
        End With
        '以下略

    End With
End Sub

'Closeボタン
Private Sub CommandButton2_Click()
    Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = Range("C2:J2").Value
End Sub

Searchボタンクリック時ではなく、コンボボックスで選択したら即検索したい場合は、ClickイベントComboBox1_Click()に記述します。Changeイベントだと一文字入力しただけでも発生しますので、適切ではないですね。コンボボックスの場合、テキストに直接入力することを可能ですので。

Answer 2

たぶん、フォームにあるSerchボタンをクリックしたときに抽出（標準モジュールのtest関数）を実行したいということですよね。
そうであれば、以下の点を変更してみてはいかがでしょうか。

抽出処理について

まず抽出処理(Test関数)の部分ですが、現状では"ぶどう"固定での検索となっているようですね。
検索キーワードは外部からもらえるように変更しましょう。

以下の記述では、検索キーワードを引数として受け取れるよう変更しています。

Sub Test(ByVal MT As String)
    Dim X, Y As Long
    Dim HHP(6), ECO(6), AAA As String
    
    'MT = "ぶどう"
    
    '(以下略)
End Sub

フォーム側の処理について

コンボボックスで選択したキーワードの取得方法ですが、検索する文字列がコンボボックスに表示している文字列そのものならComboBox1.Textだけで取得できます。

Searchボタンのクリックイベント( CommandButton1_Click)内で抽出処理を呼び出す際、コンボボックスの選択内容を引数として渡してあげましょう。

Private Sub CommandButton1_Click()
    Call Test(Me.ComboBox1.Text)
End Sub

抽出処理の中身などは確認していませんが、上記で処理の流れは理解いただけるのではないかと思います。
参考になれば幸いです。

Answer 3

こんな感じでしょうか？

モジュールの住み分け

モジュール	機能
[code:Module1]	フォームの呼び出しモジュール
[UserForm1]	実際にデータを検索するフォーム

やりたいこと

• フォーム上のコンボボックスに「りんご」「みかん」などの個々の要素を格納するところまでできた
• ここから、「りんご」だとか「みかん」などを選択したときに、これをキーにして検索したい

この「やりたいこと」を処理の流れに書き直します。

処理の順番)

1. コンボボックスを選択した（中身が変わった）とき
2. コンボボックスの中身を取得
3. コンボボックスの中身で検索

対策

もうほとんどできているように見受けられます。

1. コンボボックスを選択した（中身が変わった）とき
フォームの中にComboBox1.AddItem Worksheets("DATA").Cells(2, K).Valueとありますので、コンボボックスの中身が変わったときには、ComboBox1が変わったとき、というイベントで拾えます。具体的には、Private Sub ComboBox1_Change()というイベントハンドラです。

2. コンボボックスの中身を取得
コンボボックスの中身は、コンボボックスの選択中のインデックス番号（一番上なら0、2番目なら1といった具合です）で拾うことができます。具体的にはstr = ComboBox1.List(ComboBox1.ListIndex)です。

3. コンボボックスの中身で検索
検索処理自体は、Module1のMT = "ぶどう"以下と同じですね。UserForm1に、MT = str以下略で続ければよいと思います。

続けて書くと、

Private Sub ComboBox1_Change()
    ' コンボボックスの中身を文字列にする
    MT = ComboBox1.List(ComboBox1.ListIndex)

    ' 検索処理（Module1のEnd Subまで使いまわしOK）
    With ThisWorkbook.Worksheets("DATA")
        ' 以下略
End Sub