エクセルシート上の値をUFリストに表示し、コンボボックスから部分一致検索し表示、選択できるマクロです。
今マクロの応用によりメール作成時のメアド取得が可能です。今回のマクロは下記動的メール作成システムの内容の部分機能です。
使用するマクロによりスクリプト内の変数やオフセット値を修正してください。
・挙動
指定シートのCurrentregionから表形式の値をそのままリストボックスに表示します。UF上のリストボックスに値を入れることで部分一致検索が可能です。
この機能により、メアドが膨大な量があってもすぐに検索可能です。また、個人の検索だけでなく、GroupAdrではグループ宛先シートの登録値を検索できます。
例えばグループ宛先シートに下記のように登録しておけば、宛先とCCを一気に選択して抽出することも可能です。
また、結合セルもリストボックス上に適切に一値ずつ表示され、取得されるようになっています。
例えばグループ宛先で書きのような表示になっているとします。通常であればリストボックス上でCurrentregion表示すると結合セルは左上のみ表示され、値表示、取得ができません。
結合セルがっても今回のマクロ使用により下記のように自動結合セル埋め合わせがされ、値がそれぞれのセルに表示されます。選択は複数可能で、結合セルの空欄となってしまう個所でも値が取得できます。
これにより視覚化の為の結合セルを使用した場合でも、リストボックスで問題なく表示、抽出が可能です。
今回はメール用メアド宛先とCCを取得する機能としていますので、選択結果は下記になります。
宛先シートでも同様に複数のメアドを設定すれば、例えば「営業1課」と登録して、その課員複数を一気に呼び出すこともできます。便宜上、個人メアドリストと、グループメアドリストとして分けています。
・コード
・ポイント機能
マクロでは指定シートのCurrentregionからリストボックスに反映します。結合セルがあった空欄になってしまう個所も、リストボックス上に表示し、選択時も値取得が可能です。
・値による部分一致検索
・選択値の指定セル値取得（結合セル対応）
・取得オフセット値とリストボックス表示設定
値による部分一致検索
コンボボックス入力値は、部分一致で検索ができます。
ApplyFilterToList
検索文字列に応じて ListBox の表示内容を更新します。
txtFilter_Change/txtFilter2_Changeから呼ばれる- 現在の ListBox の選択状態を保持
- 検索文字列が空なら「全件表示」
- 文字が入っていれば
Filter2Dを使って部分一致フィルタ - フィルタ後の ListBox に再表示
- 以前選択されていた行を再選択する
該当コード（抜粋）
ApplyFilterToList ListBox1, mList1All, txtFilter.text, Array(1, 2, 3, 4)
下記部分で、指定範囲のフィルター対象列を指定しています。4まで指定していることで、4までの列値に入っている値を検索対象として検索ができます。
取得する箇所はAppendByDict関数にて、第4引数にて指定します。1と指定すれば、選択した値の1列目の値が取得されます。
Filter2D関数
2 次元配列を部分一致で検索し、ヒットした行だけ返します。
- 指定された複数列（searchCols）を対象に
InStrで部分一致判定 - 一致した行だけを新しい 2 次元配列として返す
- 大文字小文字の区別や最大件数の制御もここで行う
該当コード（抜粋）
同関数の内下記判断で各行指定列の部分一致がチェックされます。
・If InStr(1, CStr(data(r, c)), query, comp) > 0 Then matched = True”
グループ宛先として、リストボックス表示する際、Currentregionでリスト格納表示していましたが、そのままだと、結合セルは左上の値のみ表示され、結合すべてのセルは空欄となってしまいました。
そこで、リスト格納時の表示及び、選択時の値を結合セルでも対応できるように関数が必要です。
UFではメールアドレスとメール宛先名を取得としています。これは固定ではないため、スクリプト内の定数とオフセット箇所を調整すれば、選択した行でどの列の値を得たいか調整可能です。
今回グループ宛先はアドレスがI列、メアドがJ列と想定し、オフセットをかけています。
・選択値の指定セル値取得（結合セル対応）
UpdateSelectionDict
ListBox の選択状態を Dictionary に反映する役割です。
- ListBox の Change イベントで呼ばれる
- 選択された行のキー（メールアドレスなど）を Dictionary に保持
- 非選択になった行は Dictionary から削除
該当コード（抜粋）
“If lb.Selected(i) Then dict.Add key, True Else dict.Remove key”
また、この辞書により、リスト表示分を選択し検査リストボックスを空欄にすると、選択値がリセットされないようになっています。
CaptureSelectionFromList
フィルタ前に「現在見えている選択状態」を辞書に取り込みます。
- ApplyFilterToList の最初で呼ばれる
- フィルタによって ListBox の表示が変わる前に、選択状態を保存する
該当コード（抜粋）
Private Sub CaptureSelectionFromList(ByVal lb As MSForms.ListBox, ByVal dict As Object, ByVal keyCol As Long)
引数にリストボックスと辞書、メアドの場所として定数指定しているKeyColを渡し、結合セルに対応したリストボックス表示ができます。
AppendByDict
選択されたキーに対応する行を全データから探し、指定列の値を取り出す機能です。
- OK ボタン押下時（CommandButton1_Click）で呼ばれる
- Dictionary に入っているキー（メールアドレス）を基準に 全データ（mList1All / mList2All）から該当行を抽出
- 指定列（メール列・名前列）の値を連結して出力用文字列に追加
- 重複メールは
seenで排除
該当コード（抜粋）
“mailOut = mailOut & CStr(allData(r, mailCol)) & ";" nameOut = nameOut & CStr(allData(r, nameCol)) & ","”
・取得オフセット値とリストボックス表示設定
今回マクロでは指定行のメアド宛先とCC列の値を取得します。取得する列オフセットを変更する場合下記AppendByDictの引数、3つ目と4つ目を変更してください。
一つ目の引数はメアド宛先取得のためのオフセットで、定数として渡しています。変更する場合は下記を変更してください。宛先とグループ宛先シートで異なるオフセットをかけています。
UF上での表示幅についても変更可能です。下記はグループ宛先シート対象ですが、1番目からpt値を記載することで1列目から順番に指定値幅の表示とできます。
UF上で検索する対象列は下記Array値の引数から増減可能です。現状1-4列格納の値を部分一致検索の対象としています。
対象は増やせますが、リストボックス上見える部分等考慮し4列目までを検索対象列としています。
