Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

オートフィルターがかかっている列を検索する機能を改良した

先日、タイトルまんまのアドイン機能を作成した。

www.excellovers.com

公開したところtwitterフォロワー様から以下の様の意見を頂いただので早速(パクって)改良してみた。

完成品

いきなり完成品の挙動 ※データは例によって例のごとくなんちゃって個人情報より f:id:ExcelLover:20200419191828g:plain

起動時は前回と同じくフィルタがかかっている列の情報を表示している。

異なっているのは、テキストボックスとチェックボックスが1つずつ追加されている。

チェックボックスにチェックを入れるとフィルターがかかっていない列も含めて、オートフィルター範囲内の列をすべて表示するようにした。

またテキストボックスは部分検索が出来るようになっていて、入力完了後に検索ワードと一致した列のみを表示するようにした。

コード

標準モジュール。

'###オートフィルタがかかっている列を特定する
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には記載済みだけど)