ActiveXコントロール　チェックボックスでフィルターOn/Off

こんにちは。
派犬事務員のコロ子です。

最近ブログサボってました・・・。

オートフィルターって便利だけど、▽クリックして、検索文字を入力したり、チェックボックスをポチポチしたり、なんかめんどくさい。

f:id:SNegishi:20220123181840p:plain — 検索したり、ポチポチしたり

色で検索できたり、多機能だけど、日常的には文字列での検索しか使ってない。

f:id:SNegishi:20220123182606p:plain

こんな表に新規のデータを入力する。

コロ子：えっと、会社名は○○
Aさん：ちゃんと○○株式会社と入力してください。後でフィルターするときの検索が面倒になるので。
コロ子：はい。すみません。
コロ子：フルーツ、すいか
Aさん：フルーツじゃなくて果物と入力してください。それから、すいかは果物ではなく野菜です。
コロ子：すみません・・・。
Aさん：入力するときに過去に同じようなのがあるのでそれを参考にしてください。
オートフィルターで絞って探せますよ。前回の入力時に注意事項も入力しているので、そちらも必ず参考にしてください。
コロ子：はい・・・。

フィルタでポチポチしながらデータを絞る。
○○株式会社はみかんだけど、オレンジでもいいのかー。注文書に今回はグレープフルーツも可とかいてある。よし、備考に書いておこう。
△△株式会社の胸肉は皮なしなのね。

最初はいいけど、これを1日何十回。
フィルタの解除も▽をクリックして「全て選択」をポッチ。しかも解除すると、なんかへんな位置にカーソルが行ってる。今入力してるの一番下の行なんだけど！
ちっちゃいイラっとも溜まればストレス。
なのでチェックボックスのOn/Offでフィルターできる機能を付けてみた。

チェックオン：カーソル位置の値で絞る
チェックオフ：絞り解除。元のカーソル位置に戻る

ActiveXコントロール　 チェックボックス

先ずは、チェックボックスを挿入する

開発タブ
↓
挿入
↓
チェックボックス
を選択する
f:id:SNegishi:20220123205921p:plain

先頭行に配置する
f:id:SNegishi:20220123210343p:plain

先頭行は固定にしておく。（フィルター機能付けてるので当然だけど）
f:id:SNegishi:20220123211153p:plain

準備できたらコードを書いてみよう。
ActiveXコントロールはシートのイベントが使える。素晴らしい！

f:id:SNegishi:20220123212005p:plain

シートモジュールのオブジェクトボックスに先ほど作成したCheckBox1~5がいる。
となりのプロシージャボックスからClickを選択する。

'最初のカーソル位置を記憶しておく
Private myRan As Range 

Private Sub CheckBox1_Click()
    Call CheckBox_Filter(CheckBox1.Value, 3)
End Sub

Private Sub CheckBox2_Click()
    Call CheckBox_Filter(CheckBox2.Value, 4)
End Sub

Private Sub CheckBox3_Click()
    Call CheckBox_Filter(CheckBox3.Value, 5)
End Sub

Private Sub CheckBox4_Click()
    Call CheckBox_Filter(CheckBox4.Value, 6)
End Sub

Private Sub CheckBox5_Click()
    Call CheckBox_Filter(CheckBox5.Value, 7)
End Sub

'ckBox As Boolean チェックボックス値（オン/オフ）
'c As Long フィルターする列
Private Sub CheckBox_Filter(ckBox As Boolean, c As Long)

    Application.ScreenUpdating = False
    
    If ckBox Then
    
        '元のカーソル位置を記憶（チェックボックスが全てオフのとき）
        If myRan Is Nothing Then
            Set myRan = ActiveCell
        End If
        
        '元のカーソル行でチェックが入ったチェックボックスの列の値を含む文字列でフィルタする
        Dim searchStr As String
        searchStr = Cells(myRan.Row, c)
        
        Range("A1").AutoFilter c, "*" & searchStr & "*"
        
    Else
    
        Range("A1").AutoFilter 'オートフィルタをはずす
        Range("A1").AutoFilter 'オートフィルタを付ける
    
        '全てのチェックボックスのチェックをはずす
        Dim ckb As OLEObject
    
        For Each ckb In OLEObjects
    
            If InStr(ckb.Name, "CheckBox") <> 0 Then
                ckb.Object.Value = False
            End If
    
        Next
        
        Application.ScreenUpdating = True
        
        '最初の位置を選択してmyRan変数をリセット
        If Not myRan Is Nothing Then
            myRan.Select
            Set myRan = Nothing
        End If

    End If
    
Application.ScreenUpdating = True
    
End Sub

Range("A1").AutoFilterはオートフィルターがない場合はつける、ついている場合は外す。
Range("A1").AutoFilterでオートフィルターを外すと絞り込みを解除するだけではなく、オートフィルター自体も解除されてしまうので、もう一度Range("A1").AutoFilterでオートフィルターを付ける。何かダサいので多分もっといい方法があると思う。

複数チェックボックスにチェックを外す場合
ckb.Object.Value = False
でいちいちイベントが発生してしまう。
Application.EnableEvents = False
ckb.Object.Value = False
Application.EnableEvents = True
でイベントの発生を無効にしようと思ったけど、上手くいかない。
ActiveXコントロールはApplicationのイベントではないのかな？