[[20210812153058]]　『特定の操作のみ許可してシートを保護したい』（でる）　ページの最後に飛ぶ

『特定の操作のみ許可してシートを保護したい』（でる）

特定の操作のみ許可してシートを保護したいのですが、設定できません。
VBAで可能な場合は記載方法をご教示いただきたいです。

■シートの構成
・1～5行目
　6行目以下にある表の集計結果、マクロの実行ボタンがある
　グループ化して普段は閉じている
・6行目
　表のタイトル行
・7行目以下
　表のデータ入力部分

ファイル＞現在のシートの保護から下記の操作を許可してシートを保護。
（1～6行目：セルロック、7行目以下：セルロック解除）

上記の方法でシートを保護すると、1～5行目のグループ化の解除、
マクロボタンの実行ができなくなってしまいます。1～5行目は集計
結果がありデータを上書きできないように保護をかけたいのですが、
保護をかけるとグループ解除とマクロが使えず困っています。

＜使用 Excel：Excel2016、使用 OS：unknown ＞

当方2019なので違うかもしれませんがシートが保護されていてもマクロは実行できます
マクロ中の操作で保護されている内容に引っかかるためエラーが出るわけではないのですか？
それならマクロの最初でシートの保護を解除して最後に保護を有効にすれば良いのではないでしょうか
グループ化もマクロに組み込んでしまってはダメなのでしょうか
（砂糖） 2021/08/12(木) 16:39

砂糖さん返信ありがとうございます。2016だとシートが保護されているとマクロは実行できません。
実行時エラー1004：保護されたシートに対して、このコマンドは使用できません（以下略）が出ます。
マクロ中の操作ではなく、保護をしていると実行自体ができないようです。グループ化されている
5行目までは別のユーザが作成しており、できれば変更したくないです。

因みに以下のVBAをThisWorkbookに記載したところグループ化の解除とオートフィルタは使用できる
ようになったのですが、今度はセルロックしていない7行目以下も行挿入や書式設定などができなく
なってしまいました。シート保護の各オプションの設定を下記に追記してVBAで制御できるとよいの
ですが、記載方法がいまいち分からずうまくいきません。。

Sub 保護したまま実行()

    With Worksheets("Sheet1")
        .EnableOutlining = True
	.EnableAutoFilter = True
        .Protect UserInterfaceOnly:=True
    End With
End Sub

（でる） 2021/08/12(木) 16:55

 5行目までをいじられたくないだけなら、画面をスクロールして6行目から見える状態で
 ウィンドウ枠を固定してはどうでしょう？

 固定した状態を表示タブ→ユーザー設定のビューに登録しておけば、一旦解除して
 5行目までのデータを確認したら、またすぐに元の状態に戻すことができますが。
（コナミ） 2021/08/12(木) 16:57

 >シート保護の各オプションの設定を下記に追記してVBAで制御できるとよい
シート保護をかける操作をマクロの記録すると、どう書けばいいかわかります
365で全部許可するようにするとこんな風になります。
バージョン依存だと思うので、2019でマクロの記録して確かめて下さい

    ActiveSheet.Protect UserInterfaceOnly = True, _
                        DrawingObjects:=False, _
                        Contents:=True, _
                        Scenarios:=False, _
                        AllowFormattingCells:=True, _
                        AllowFormattingColumns:=True, _
                        AllowFormattingRows:=True, _
                        AllowInsertingColumns:=True, _
                        AllowInsertingRows:=True, _
                        AllowInsertingHyperlinks:=True, _
                        AllowDeletingColumns:=True, _
                        AllowDeletingRows:=True, _
                        AllowSorting:=True, _
                        AllowFiltering:=True, _
                        AllowUsingPivotTables:=True
（とおりすがり） 2021/08/12(木) 17:10

＞コナミさん
返信ありがとうございます。ユーザー設定のビュー機能は知りませんでした。
デフォルトでグループ化して表示を隠しているのですが、解除してデータを
上書きされてしまったことがあり、セル自体を編集できないようにしたいです。

＞とおりすがりさん
返信ありがとうございます。下記のコードに追加して動作を確認したところ、
シートの保護をかけた状態でグループ化の解除とマクロボタンの実行ができ、
ロックしたセルは編集不可にすることができました。あとは不要なオプション
だけFalseに変更します。解決して大変助かりました。ありがとうございました。

Private Sub Workbook_Open()

    Sheets("Sheet1").EnableOutlining = True
    Sheets("Sheet1").Protect DrawingObjects:=True, _
    Contents:=True, UserInterfaceOnly:=True

    ActiveSheet.Protect UserInterfaceOnly = True, _
                        DrawingObjects:=False, _
                        Contents:=True, _
                        Scenarios:=False, _
                        AllowFormattingCells:=True, _
                        AllowFormattingColumns:=True, _
                        AllowFormattingRows:=True, _
                        AllowInsertingColumns:=True, _
                        AllowInsertingRows:=True, _
                        AllowInsertingHyperlinks:=True, _
                        AllowDeletingColumns:=True, _
                        AllowDeletingRows:=True, _
                        AllowSorting:=True, _
                        AllowFiltering:=True, _
                        AllowUsingPivotTables:=True

End Sub

（でる） 2021/08/12(木) 21:07

UserInterfaceOnlyは再起動すると無効化されるはずだけど大丈夫なのだろうか
（砂糖） 2021/08/13(金) 08:39

砂糖さん返信ありがとうございます。下記を試しましたが、正常に動作しました。

■テストした内容
・Excelを開いてそのまま上書き保存し、ファイルを開きなおす
　→VBAに記載された内容が有効
・Excelを開いてシート保護を手動で解除して上書き保存し、ファイルを開きなおす
　→VBAに記載された内容が有効
・Excelを開いてシート保護を手動で解除して、手動で再度シート保護（オプション変更）
　して上書き保存し、ファイルを開きなおす
　→VBAに記載された内容が有効

上記テストだと、常にファイルを開いた時にVBAの処理が有効になっているように思えます。
具体的にどういった操作をすると無効化されてしまうのかご教授いただけると幸いです。
よろしくお願いいたします。
（でる） 2021/08/13(金) 14:45

http://officetanaka.net/excel/vba/sheet/sheet07.htm

↑から抜粋

 なお注意しなければいけないのは、引数UserInterfaceOnlyにTrueを指定して保護したワークシート
 をそのまま保存して閉じた場合です。次にそのブックを開いたときには、ユーザーの手動操作だけで
 なくマクロによる操作も保護されています。マクロ操作だけを保護の対象からはずすには、再度引数
 UserInterfaceOnlyにTrueを指定してProtectメソッドを実行しなければなりません。

要は、 UserInterfaceOnlyにTrueを設定すると、【マクロ】からのシート変更は有効になるのだが、ブックを開き直すと普通の保護（マクロからの変更も不可）になるということでしょう。

（もこな２） 2021/08/13(金) 15:11

もこな２さん返信ありがとうございます。リンクと説明見ました。
シート保護した状態でそのまま保存したのですが、動作しています。
偶然なのですが、重複してコードを記載していたのが結果的によかったのかもしれません。。
構文としておかしい場合はご指摘いただけると助かります。

    Sheets("Sheet1").EnableOutlining = True
    Sheets("Sheet1").Protect DrawingObjects:=True, _
    Contents:=True, UserInterfaceOnly:=True

    ActiveSheet.Protect UserInterfaceOnly = True, _
                        DrawingObjects:=False, _
                        Contents:=True, _
                        Scenarios:=False, 
 　　　　　　　　　　　 '以下略：各オプションを指定

上記で、ロックセルは編集不可になり、グループ化とマクロボタン実行は可。
アンロックセルはオプションで指定した内容が設定されています。保存して
ファイルを開きなおした場合も、都度VBAの内容が有効化されています。

試しに上段のUserInterfaceOnly:=Trueを消去したところ、グループ化と
マクロは実行できましたが、ロックセルが編集可能になってしまいました。
VBAの内容が部分的に有効化されているようです。

下段のUserInterfaceOnly:=Trueを消去したところ、グループ化とマクロは
実行できなくなりました。普通の保護（マクロからの変更も不可）の状態
だと思われます。

（でる） 2021/08/13(金) 16:05

マクロの最初に
ActiveSheet.Protect UserInterfaceOnly = True
としているのだったらUserInterfaceOnlyを設定していないシートの保護を
UserInterfaceOnlyを設定しているシートの保護で上書きしているので動作しているんじゃないでしょうか
これが動作しているということならやっぱり最初にUnprotectしても動くような気がします。
（砂糖） 2021/08/13(金) 16:12

コメント返信：

[ 一覧(最新更新順) ]

[[20210812153058]] 『特定の操作のみ許可してシートを保護したい』（でる） ページの最後に飛ぶ

[[20210812153058]]　『特定の操作のみ許可してシートを保護したい』（でる）　ページの最後に飛ぶ