エクセルで閉じるときのマクロ

質問

あるエクセルファイルがあり、
その一枚のシートにはオートフィルタ
（A～Z列まで）を使用しています。
【質問1】
ファイルを保存して閉じる際、
オートフィルタで抽出したものを
「すべて」に戻す、
つまりなにも抽出されていない（＝左端の行番号が黒字）
に戻すマクロはどこにどのように書けばよいでしょうか？

オートフィルタを戻すマクロは、

'オートフィルタのあるシートを選択
Sheets("買取リスト").Select
'T列のオートフィルタを「すべて」にする
Selection.AutoFilter Field:=22
というところまでは理解できました。

【質問2】
上記のマクロを
Sheets("顧客リスト")
から、Sheets("商品リスト")へ移動した際、
ボタン等を使用せずに、
（ただ、下のタブをクリックするだけで）
実行させることは可能でしょうか？
可能であれば、どこにどのように記述すれば
よいでしょうか？？

　よろしくお願いします。

回答No.5

vbafriend · ベストアンサー

こんばんわ。
これが30本あれば、一本一本すべてにしていくので、時間がかかるのではないかと危惧して、・・・・・・・・
すっきりとした１発解除方法は無いようです。オートフィルターを一度画面から消して再度同じ場所に表示するという方法を取るか、Selection.AutoFilter Field:=22をオートフィルターの数だけ書くという方法しかないようです。オートフィルターの理屈を考えてみれば、お解りになると思います。

＞質問2に関しては、ワークシートのタブをダブルクリックする習慣が私も含めありませんので、できればシングルクリックで、実行できる方法があればご教示ください。

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

ここに、コードを書く。

End Sub

シートが選択されてアクティブになった時点でマクロが走り出します。

ご不明な点がございましたらご遠慮なく補足要求して下さい。

お礼

とても参考になりました。
何度もお答えいただき、ありがとうございました。

回答No.6

papayuka

再びこんにちは。

A1から始まるフィールドデータを想定してたので、UsedRangeで良いかと思ったんですが各シート毎にフィルター範囲が異なるなら、それを保持するようにしないとダメです。

標準モジュールに書く

Sub Auto_Close()
Dim ws As Worksheet
Dim r As Range
　For Each ws In Worksheets
　　If ws.AutoFilterMode Then
　　　Set r = ws.AutoFilter.Range
　　　ws.AutoFilterMode = False
　　　r.AutoFilter
　　End If
　Next ws
　Set r = Nothing
End Sub

ThisWorkBookに書く

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim r As Range
　If Sh.AutoFilterMode Then
　　 Set r = Sh.AutoFilter.Range
　　 Sh.AutoFilterMode = False
　　 r.AutoFilter
　End If
　Set r = Nothing
End Sub

前回の最後にも書いた通りサンプルです。
どんなシート構成でどんな使用を想定しているか等、言葉で伝えるのは難しいですし、こちらは想像でしか書けません。
ご自身で修正する努力も必要かと思いますよ。

お礼

＞ご自身で修正する努力も必要かと思いますよ。
おっしゃる通りですね。
何度もお答えいただきまして、
ありがとうございました。

お蔭様で希望どおりのものができました。

回答No.4

vbafriend

こんにちは。
▼を残したまま、抽出を解除する方法はありませんでしょうか？
Selection.AutoFilter Field:=22この式で、ボタンは消えずに抽出は解除できると思うのですが、何か不具合があるのでしょうか？どのような不具合なのかを教えて下さい。

＞質問2に関しては、ワークシートのタブをダブルクリックする習慣が私も含めありませんので、できればシングルクリックで、実行できる方法があればご教示ください。

ダブルクリックで実行するのが一番早い解決方法なのですが、どうしてもお好みで無いならば、マクロを走らせるタイミングと貼り付けするシートのシート名を教えて下さい。

お手数をおかけいたします。よろしくお願いいたします。

補足

> Selection.AutoFilter Field:=22
これは、22列目（T列）のフィルタを
すべてにする式ですよね。
これが30本あれば、一本一本すべてにしていくので、
時間がかかるのではないかと危惧して、
一発解除（ただし、フィルタは残したまま）
はないものかと思って、再質問させていただいた次第です。

＞マクロを走らせるタイミング
任意のタブをクリックした場合
シート名
買取
得意先
在庫

＞貼り付けするシートのシート名
list
が、フィルターを解除したいシート名です。

どうぞよろしくお願いします。

回答No.3

vbafriend

初めまして。
【質問1】
オートフィルターがかかっているブック名をＡ・行を１行目として
workbooks("Ａ.xls").worksheets("シート名").rows("1:1").Autofilter
これでオートフィルターが削除され、選択されていない状態に戻ります。

【質問2】
ThisWorkbookのモジュールシートで下記のイベントを利用すればよろしいかと思います。

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

End Sub
これを使うと、シートのタブをダブルクリックした時にマクロが走り出します。

ご不明な点等がございましたら、ご遠慮なく補足要求して下さい。

補足

上記マクロを実行すると、
オートフィルタが解除されてしまい、
▼のアイコンがなくなってしまいます。
▼を残したまま、抽出を解除する方法はありませんでしょうか？

質問2に関しては、
ワークシートのタブをダブルクリックする習慣が
私も含めありませんので、できればシングルクリックで、
実行できる方法があればご教示ください。

回答No.2

papayuka

こんにちは。
標準モジュールに下記でブックを閉じる時にフィルターを全て戻します。
Workbook_BeforeClose イベントでも同じです。

Sub Auto_Close()
Dim ws As Worksheet
　For Each ws In Worksheets
　　If ws.AutoFilterMode Then
　　　 ws.AutoFilterMode = False
　　　 ws.UsedRange.AutoFilter
　　End If
　Next ws
End Sub

ThisWorkbookに下記で、アクティブになった時にフィルターを解除します。

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
　If Sh.AutoFilterMode Then
　　 Sh.AutoFilterMode = False
　　 Sh.UsedRange.AutoFilter
　End If
End Sub

サンプルなので、Range指定がシートによっては上手くいかないかも。。。

お礼

ご教示ありがとうございます。
早速試したところ、
おおっ！っと思ったのですが、
よくみると、
オートフィルタのある行がずれていました。

オリジナルは、
シート1は、A10：AO10にあったのがA1：CS10に。
シート2は、A3：AO3にあったのが、A2：BC2に、
フィルタが移動してしまいました。
この解決策はございますか？

回答No.1

BraveStory

質問1
VBEditor の　オブジェクトエクスプローラー　で　ThisWorkbookをダブルクリックします。
表示されたコード内で、次のいずれかのイベントプロシージャの中にマクロを記入してみてください。
'--------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)

End Sub
'--------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

End Sub
'--------------------------------------------------------
Private Sub Workbook_Open()

End Sub

それぞれの違いについてはヘルプ等をご参考に・・・

質問2
質問1と同じ場所で次のプロシージャの使います
'--------------------------------------------------------
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub