仕事中にExcel VBAでのオートフィルターの解除について色々調べたので共有しておきます。
調べて行った流れで書いているので、手っ取り早くソースが知りたい方は記事の下の方に回答があるのでそちらをどうぞ。
まずネットで検索してよく出てくるソースは以下
---------------------------------------------------------------------------------If .AutoFilterMode And .AutoFilter.FilterMode Then
上記のソースを、ボタンなどに仕込んでおいて、ボタンが押されたときに解除する等の処理を行います。
解説をすると、1行目ではオートフィルターが設定され、かつ絞り込まれているかを確認しています。この確認を入れない場合、フィルターモードが解除されていたり、絞り込みがされていない場合に実行時エラー1004が返されます。
1行目の結果が両方Trueであれば、続く2行目でオートフィルターによる絞り込みを全て解除しています。
通常はこれで絞り込みは解除されるのですが、状況によってはエラーが返されて使えないことがあります。
１．シートの保護をしている場合
シートの保護をしている場合は、ActiveSheet.ShowAllDataは実行時エラー1004を返します。
ネットでよく見かける解決法としては、ActiveSheet.ShowAllDataを実行する時だけシートの保護を解除し、実行後再び保護をするという物でした。ソースは以下。
If .AutoFilterMode And .AutoFilter.FilterMode Then
End If
お分かりと思いますが、3行目でシートの保護を解除し、5行目でオートフィルターの使用を許可しつつ再びシートの保護をしています。
シートの保護時の各許可のパラメータについてはMicrosoftのこちらのドキュメントにまとめられています。
これで確かにシートの保護をしていても絞り込みを解除できるのですが、これでも困る事態になる場合があります。
２．ブックの共有をする場合
ブックの共有を行うと以下の作業が出来なくなります。
・Excelテーブルの挿入
・セル範囲の挿入または削除（行全体や列全体はOK）
・ワークシートの削除
・グラフやピボットグラフの作成や変更
・図などオブジェクトの挿入または変更
・ハイパーリンクの挿入や変更
・ワークシートやブックの保護やその解除
・自動集計の挿入
・マクロの記録や変更（使用できない機能を使わないマクロの実行はOK）
・配列数式の変更または削除
つまりブックの共有時はワークシート保護の解除／再保護が出来ません。
ブックの共有を解除／再共有をするという処理も出来るようですが、その場合共有が解除されるので、他に共有ブックを開いているユーザがいる場合、全員に閉じてもらう必要があります。
回答
VBAでオートフィルターの絞り込み解除をする処理のソースはこちら
---------------------------------------------------------------------------------If .AutoFilterMode And .AutoFilter.FilterMode Then
どこが違うかと言うと絞り込み解除の処理が
ActiveSheet.ShowAllData から ActiveSheet.AutoFilter.ShowAllData になっています。
ActiveSheet.AutoFilterはフィルター処理がオンの場合、AutoFilterオブジェクトを返します。 （リファレンス）
おそらくシートの保護を行うと、保護を行ったシートのWorkSheetオブジェクトに対する操作をブロックするのに対して、AutoFilterオブジェクトに対しては保護の影響は及ばないのではないでしょうか。
なのでシートの保護をした状態でも.AutoFilter.ShowAllDataはエラーが返されずに無事実行されるという事ですね。
結果、一時的にシートの保護の解除をするという手間も省けて、ソースもスッキリした形で絞り込み解除が実装できたかと思います。
仕事上で、複数人で一つのExcelファイルを共有して作業を進める事があるんですが、作業をしている人の中に絞り込みをしたまま解除せず保存する人が居たので、いちいち直すのが面倒だったんですね。
しかもウィンドウ枠の固定もしていたので、絞り込んだまま保存されると固定の位置がおかしい事になってしまうのです。
それを解決すべく、保存時に強制的に絞り込みを解除する処理を組み込んだわけです。
調べるのに中々時間を要しました。どなたかの助けになれば幸いです。
0 件のコメント:
コメントを投稿