EXCEL VBAで条件付き保護について

質問

VBA初心者です、よろしくお願いします。

列Eでオートフィルタを使用可能と設定したいので、下記のようにしましたが、「シートの保護を設定」のステップを終えた後オートフィルタも使用できません。

「AllowFiltering:=True」でEXCELのオートフィルタの使用にチェックを入れた状態になると考えていましたが、思うようになりませんでした。
「シートの保護を設定」のステップがおかしいと思うのですが、わかりません。

教えてください。

------------------------------------------------------
Private Sub Auto_Open()
'一旦、シート保護を解除
ActiveSheet.Unprotect Password:="AAA"
列Hのデータを重複データを無視で列Oに抽出
セルH２は「入力規則－リスト」～列Oのデータを使用
　　・
　　・
　　・
' シート保護を設定
ActiveSheet.Protect Password:="AAA", UserInterfaceOnly:=True, AllowFiltering:=True
End Sub

回答No.2

end-u · ベストアンサー

「シートの保護を設定」のステップ は特におかしくはないです。

>..オートフィルタも使用できません。
とは、
A)既に設定してあるオートフィルタのフィルタ条件を変更する事
B)新規にオートフィルタ範囲を設定する、または既フィルタの解除をする事
のどちらの意味でしょうか。

(B)の意味の場合、AllowFiltering:=True を設定してもそれはできません。
元々の仕様です。
Protectメソッドのヘルプを見てください。

<help引用>
AllowFiltering
省略可能です。
バリアント型 (Variant) の値を使用します。
True を指定すると、ユーザーは保護されたワークシートにフィルタを設定することができます。
ユーザーは、フィルタ条件を変更できますが、オート フィルタの有効と無効を切り替えることはできません。
既定値は False です。
</引用>

特定範囲にオートフィルタを設定して、解除操作を保護しておきたい場合は
Auto_Openマクロ内でそのように記述すれば良いです。

オートフィルタの設定|解除操作も可能にしたい場合は、UserInterfaceOnly:=True にしてありますから、
その動作をマクロで組めば良いです。
マクロの実行についてはシートにボタンを設定したり、右クリックメニューに追加したり、
などが考えられます。

（右クリックメニュー設定例）
Option Explicit
'-------------------------------------------------
Private Sub Auto_Open()
　　ActiveSheet.Unprotect Password:="AAA"
　　':
　　ActiveSheet.Protect Password:="AAA", _
　　　　　　　　　　　　UserInterfaceOnly:=True, _
　　　　　　　　　　　　AllowFiltering:=True
　　Call barSet
End Sub
'-------------------------------------------------
Private Sub Auto_Close()
　　Dim c As CommandBar

　　For Each c In Application.CommandBars
　　　　If c.Name = "Cell" Then
　　　　　　On Error Resume Next
　　　　　　c.Controls("AutoFilter").Delete
　　　　　　On Error GoTo 0
　　　　End If
　　Next
End Sub
'-------------------------------------------------
Private Sub barSet()
　　Dim c　As CommandBar
　　Dim cc As CommandBarControl

　　For Each c In Application.CommandBars
　　　　If c.Name = "Cell" Then
　　　　　　On Error Resume Next
　　　　　　Set cc = c.Controls("AutoFilter")
　　　　　　On Error GoTo 0
　　　　　　If cc Is Nothing Then
　　　　　　　　With c.Controls.Add(Type:=msoControlButton, _
　　　　　　　　　　　　　　　　　　Before:=1, _
　　　　　　　　　　　　　　　　　　Temporary:=True)
　　　　　　　　　　.Caption = "AutoFilter"
　　　　　　　　　　.OnAction = "aFilter"
　　　　　　　　End With
　　　　　　End If
　　　　End If
　　Next
End Sub
'-------------------------------------------------
Private Sub aFilter()
　　On Error Resume Next
　　Selection.AutoFilter
End Sub
'-------------------------------------------------

回答No.4

doredora

こんばんは。

ご質問の回答に的を得ていないかもしれませんが、マクロ処理の際に処理の前に保護解除して処理後に保護をする記述が煩わしくなりThisWorkbookに

Private Sub Workbook_Open()
　　Sheets("シート名").Protect Password:="AAA", userinterfaceonly:=True
End Sub

を記述するようにしています。
これは、マクロがそのシートを"AAA"というパスワードを保有しマクロ実行時にそのパスワードを使用し解除、保護をその都度やってくれます（表現的に正しいかわかりませんが私はそんな感じで使っています）
これを記述するようになってからは記述する際に気にする要素がひとつ減り楽になっています。

今回の問題がシート保護をかけるタイミング云々が関係しているようなら試してみられてはいかがでしょう

お門違いの内容ならスルーしてください＾＾；

回答No.3

WindFaller

#1の回答者です。

メニューの中のオートフィルタをオン・オフさせるということのようですね。
メニューの中で、フィルタ・コマンドは、グレーになってしまっています。

もし、そういうことでしたら、シート上に、コマンドボタンを設けたらどうですか？

>列Hのデータを重複データを無視で列Oに抽出
>セルH２は「入力規則－リスト」～列Oのデータを使用
この文章では、オートフィルタがどこに書かれてあるのか、良く分かりませんが、
例えば、O列にあるとしたら、以下のようにします。

Private Sub CommandButton1_Click()
　　Me.Range("O1").AutoFilter　'オートフィルタのデータの左上端
End Sub

もし、コマンドボタンが格好が悪いようでしたら、セルの形に合わせて、透明なコマンドボタンという方法もあります。プロパティで、Caption の中の字を消し、BackStyle を、0-fmBackStyleTranparent にすれば、透明になります。ただ、これではわかりづらいので、BackColor を適当な色をつけてあげてもよいと思います。

こちらの解釈で、回答の意味が違っていたら、ご容赦ねがいます。

回答No.1

WindFaller

こんにちは。

これは、VBAの問題ではありません。
「AllowFiltering:=True」は、あくまでも、オートフィルタ等に対してで、入力規則のリストは、セルの値の変化ですから、セルの書式の中の「保護」のロックを外してあげなくてはなりません。

お礼

説明が下手で申し訳ございません。
リスト入力はうまくいっております。
オートフィルターが使えない状態です。