先日、タイトルまんまのアドイン機能を作成した。
公開したところtwitterフォロワー様から以下の様の意見を頂いただので早速(パクって)改良してみた。
私も職場では横に長～～～～いテーブルを多用しているんです。でも気が付きませんでした。
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2020年4月18日
ところで、フィルタの有無に関わらず列名をリストにして、インクリメンタルサーチしてジャンプできるようにしても使える気がしてきました。
検索機能のUIでは微妙に使いづらいですから。
完成品
いきなり完成品の挙動
※データは例によって例のごとくなんちゃって個人情報より
起動時は前回と同じくフィルタがかかっている列の情報を表示している。
異なっているのは、テキストボックスとチェックボックスが１つずつ追加されている。
チェックボックスにチェックを入れるとフィルターがかかっていない列も含めて、オートフィルター範囲内の列をすべて表示するようにした。
またテキストボックスは部分検索が出来るようになっていて、入力完了後に検索ワードと一致した列のみを表示するようにした。
コード
標準モジュール。
'###オートフィルタがかかっている列を特定する Function AutoFilterSearch(AllColumnSearch As Boolean) As Variant Dim i As Long Dim n As Long Dim myVar() As Variant With ActiveSheet.AutoFilter If ActiveSheet.AutoFilterMode Then ReDim myVar(.Filters.Count) For i = 1 To .Filters.Count If AllColumnSearch Or .Filters(i).On Then myVar(n) = .Range.Cells(i).Value & "," & .Parent.Name & "," & .Range.Cells(i).Address(False, False) n = n + 1 End If Next End If End With If n = 0 Then myVar = Array() Else ReDim Preserve myVar(n - 1) End If AutoFilterSearch = myVar End Function
ユーザーフォーム
Private Sub TextBox1_AfterUpdate() ListboxAdd End Sub Private Sub UserForm_Initialize() Call ListboxAdd Me.ListBox1.SetFocus End Sub Private Sub CheckBox1_Change() Call ListboxAdd Me.TextBox1.SetFocus End Sub Sub ListboxAdd() Dim myVar As Variant myVar = AutoFilterSearch(Me.CheckBox1.Value) Me.ListBox1.Clear Dim i As Long For i = 0 To UBound(myVar) If myVar(i) Like "*" & Me.TextBox1.Value & "*" Then Me.ListBox1.AddItem myVar(i) Next End Sub Private Sub ListBox1_Click() 'リストボックスに表示された検索結果をクリックすると、検索結果へ移動する Dim Adr As String Dim myWS As Worksheet Dim myList As Variant myList = Split(ListBox1.List(ListBox1.ListIndex), ",") Set myWS = Worksheets(myList(1)) Adr = myList(2) myWS.Activate Range(Adr).Activate End Sub
こちらはユーザーフォーム項目が増えたことによってコードも増えている
AutoFilterSearch関数
前回からの変更点は
・引数AllColumnSearchを設定
・上記変更に伴い列名を取得する判定ロジックを変更
引数AllColumnSearchはユーザーフォームに追加したチェックボックスの値を受け取るために設定しました。
このチェックボックスにチェックが入っていればオートフィルターの状態にかからわず、オートフィルターの対象列名をすべて取得します。
チェックが外れていれば、フィルタがかかっている列名だけ取得します。
コードの変更は以下の部分です。
AllColumnSearch Or .Filters(i).On Then
ここでチェックボックスにチェックが入っているもしくはフィルタがかかっている列を判定しています。
ユーザーフォーム
こちらはユーザーフォームにパーツが増えたことでコードが増えていますが、起点が増えただけで大きくは処理内容変更していません。
ただし同じ処理が複数箇所に現れるようになったので、同一の処理をListboxAddで独立させました。
そのListboxAdd内に今回追加したテキストボックスによる検索に対応するコードを追加しました。
If myVar(i) Like "*" & Me.TextBox1.Value & "*" Then Me.ListBox1.AddItem myVar(i)
テキストボックスと部分一致した場合にリストボックスに追加するようにしました。
まとめ
これでオートフィルターに対しての検索は十分満足行くものになったのですが、テストをしているうちにある機能を追加すべきかどうかモヤモヤとしてきました。
それに関してはまた後日。(twitterには記載済みだけど)
