【Excel】他ブックからOpenされたブックはUserInterfaceOnlyが効かない？

Question

標記のとおりの質問です。

あるブックAからブックBをOpenし、
UserInterfaceOnlyでロックしたものを編集しようとするのですが、
その際に

実行時エラー '1004':
変更しようとしているセルは保護されているため、読み取り専用となっています。

とのエラーが出てしまいます。
これはブックBで単独で実行するときには出ない症状です。
他ブックから開かれたブックはUserInterfaceOnlyが作用していないのでしょうか。
因みに、B.Closeを実行した中でのWorkbook_BeforeClose関数内での動作です。

どなたかご教示ください。よろしくお願いいたします。

end-u · Accepted Answer

再現性？
【Ｑ】
'"C:\TestBook_B.xls"ThisWorkbookModule
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　On Error GoTo errHndlr
　　With ThisWorkbook.Sheets(1)
　　　　.Protect UserInterfaceOnly:=True
　　　　.Range("A1").Value = Time
　　End With
errHndlr:
　　With Err
　　　　If .Number = 0 Then
　　　　　　MsgBox "ok"
　　　　Else
　　　　　　MsgBox .Number & vbTab & .Description
　　　　　　Debug.Print .Number & vbTab & .Description
　　　　End If
　　End With
End Sub
A)上記コード単独で実行＝TestBook_B.xlsを手作業で閉じる。エラーは発生せず。

B)下記のコードを他Bookより実行＝他マクロからTestBook_B.xlsのWorkbook_BeforeCloseが呼ばれる。エラー発生。
Sub Macro1()
　　Workbooks.Open(Filename:="C:\TestBook_B.xls").Close
End Sub
　▼
【Ａ】#3
(B)のエラー発生原因は#3にてアドバイス済み。
対策も#3にて３点アドバイス済み。
C)TestBook_B.xlsのWorkbook_Openイベントで.Protect UserInterfaceOnly:=True を実行するように変更ではダメなのか。
D)他BookからTestBook_B.xlsを閉じるマクロを、手動操作をエミュレートするようなマクロに変更してはどうか。
E)他BookからTestBook_B.xlsを閉じるマクロで、Protect UserInterfaceOnly:=True を実行するようにしてはどうか。
　▼
【Ｑ】#3
(C)に対しては
>・手動でシートを解除することがある
>・ブックB単独で使うことがある
>という条件から、このような仕様になっています。
　▼
【Ａ】#5
それだけの理由ならという条件付で、Private Sub Workbook_Open()コードを提案。
（ついでに(E)の具体例も）
　▼
【Ｑ】#5
>正規ユーザ：手動解除→保存→終了　のあとに
>非正規ユーザ：ファイルを開く→マクロを実行しない
>とするとプロテクトが外れたままなのがネックとなります。
　▼
【Ａ】#8
『非正規ユーザ：ファイルを開く→マクロを実行しない』これは、マクロ無効で開くという意味ですね？
不特定多数のユーザーにマクロブックを公開する場合、マクロ無効対策も必要。
さらにUserInterfaceOnly:=TrueでのProtectは避けたほうが良いのでは。
　▼
　▼
　▼
という事でUserInterfaceOnly:=Trueを省くコードを提案。（間違っちゃいましたけどね）
結局、
>ただ、そこまでやるならuserinterfaceonly:=Trueは選択肢としては有り得ないはず。
これは私の勘違いです。撤回します。
通常の運用の時はProtect UserInterfaceOnly:=False状態である。
Workbook_BeforeCloseイベント内のコードが走る時だけUserInterfaceOnly:=Trueにすれば良い。
...ですよね。

他マクロからTestBook_B.xlsのWorkbook_BeforeCloseが呼ばれる時はProtectメソッドが効かないので
>E)他BookからTestBook_B.xlsを閉じるマクロで、Protect UserInterfaceOnly:=True を実行するようにしてはどうか。
のセンで。（Workbook_BeforeSaveイベントでのProtectも必要でしょうけど）
あるいはWendy02さんのSub MacroTest_A()、Open時にUserInterfaceonly:=Trueでも。

>このエラー・メッセージが正確なものかはわかりませんが、ご質問者さんのコードもみないで、回答者が一方的な想像で、空回りしているような気がします。どの時点で、エラーが発生しているのかは、回答者側では確認できていません。
どうも。スルドいご指摘、ありがとうございます。

Wendy02 · Answer

#6の回答者です。

もう一度読み直してみましたが、どうも、こちらに誤解があるのかな？

#5の補足から
>正規ユーザ：手動解除→保存→終了　のあとに
>非正規ユーザ：ファイルを開く→マクロを実行しない
>とするとプロテクトが外れたままなのがネックとなります。

いずれにしても、正規ユーザーが使用した後は、マクロ実行で、終了時にマクロで、プロテクトをさせるようにすればよいわけですから、こちらの話が織り込み済みだというなら、この程度の問題が解決できないはずがありません。どうも、話のつじつまが合わないような気がします。

上記の流れには、UserInterfaceOnly は、まったく関係ありません。終了時に、Protect をすればよいだけです。

一時、特殊な環境の問題だとも考えましたが、それなら、以下のようなエラーメッセージは出ません。

>実行時エラー '1004':
>変更しようとしているセルは保護されているため、読み取り専用となっています。

このエラー・メッセージが正確なものかはわかりませんが、ご質問者さんのコードもみないで、回答者が一方的な想像で、空回りしているような気がします。どの時点で、エラーが発生しているのかは、回答者側では確認できていません。

>あるブックAからブックBをOpenし、
>UserInterfaceOnlyでロックしたものを編集しようとするのですが、
というなら、以下のようなコードで済むはずです。

Sub MacroTest_A()
Const fn = "C:\TestBook_B.xls"
With Workbooks.Open(fn)
　.Worksheets("Sheet1").Protect Password:="PWS", UserInterfaceonly:=True
　.Worksheets("Sheet1").Range("A1").Value = Time　'セル入力
End With
End Sub

このまま、あれこれ書いても、こちらが再現性が得られないのでは、のれんに腕押しのようで、ちょっと、これ以上は回答は難しいなって思います。

end-u · Answer

何度もすみません。
>'標準Module
>Option Explicit
>Sub BeforeClose()
>　　With Sheets(1)
>　　　　.Unprotect
>　　　　.Cells(1).Value = Now()
>　　　　.Protect
>　　End With
>　　ThisWorkbook.Save
>End Sub

'標準Module
Option Explicit
Sub BeforeClose()
　　With ThisWorkbook
　　　　With .Sheets(1)
　　　　　　.Unprotect
　　　　　　.Cells(1).Value = Now()
　　　　　　.Protect
　　　　End With
　　　　.Save
　　End With
End Sub

初歩的なミスです。
#どうにもいけませんな...またしばらく修行のタビに出ます...
#失礼しました XD

end-u · Answer

ぅあ、全然ダメだ。ゴメンなさい。
Bclose→Workbook_BeforeCloseだとWorkbook_BeforeSaveが走らない...orz

【C:\Bbook.xls】
'ThisWorkbookModule
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
　　Sheets(1).Protect
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　Call BeforeClose
End Sub

'標準Module
Option Explicit
Sub BeforeClose()
　　With Sheets(1)
　　　　.Unprotect
　　　　.Cells(1).Value = Now()
　　　　.Protect
　　End With
　　ThisWorkbook.Save
End Sub

【他のテスト用ブック】
'標準Module
Sub Bclose()
　　With Application
　　　　.Run "Bbook.xls!BeforeClose"
　　　　.EnableEvents = False
　　　　Workbooks("Bbook.xls").Close
　　　　.EnableEvents = True
　　End With
End Sub



結局、最初の
Workbooks("Bbook.xls").Activate
Application.CommandBars.FindControl(ID:=106).accDoDefaultAction
の方がまだましかも。

end-u · Answer

>正規ユーザ：手動解除→保存→終了　のあとに
>非正規ユーザ：ファイルを開く→マクロを実行しない
>とするとプロテクトが外れたままなのがネックとなります。
『非正規ユーザ：ファイルを開く→マクロを実行しない』これは、マクロ無効で開くという意味ですね？
正規ユーザーがマクロ無効で開くという想定は必要ないという事ですかね。
正規ユーザー：マクロ無効で開く→手動解除→保存→終了
この場合、Workbook_BeforeCloseイベントのみでは不十分なのは同じ事です。
正規｜非正規ユーザーが混在し、不特定多数のユーザーにマクロブックを公開する場合、マクロ無効で開く事の対策は当然必要です。
http://homepage2.nifty.com/kmado/kvba.htm
E00M090[マクロを無効にする]で開くと使えないxlsファイル
（ここ参考にしてみてください）

ただ、そこまでやるならuserinterfaceonly:=Trueは選択肢としては有り得ないはず。
ちょっとしたマクロができるユーザーなら書き込みができてしまうという事ですから。
必要に応じてマクロ内にUnprotect/Protectを記述すれば良いのではないですか？

いずれにしてもWorkbook_BeforeSaveイベントにもProtectメソッドを記述しておけば済む話のような気がしないでもないですが。

'ThisWorkbookModule
Option Explicit

Private Sub Workbook_Open()
　　Sheets(1).Protect 'userinterfaceonly:=True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
　　Sheets(1).Protect 'userinterfaceonly:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　Sheets(1).Unprotect
　　'終了前処理
　　Sheets(1).Cells(1).Value = Now()
　　ThisWorkbook.Save
End Sub
#BeforeCloseイベント内で保護シートの内容を書き換えるという事は、その後にSaveメソッドで保存しているはず。

上記は C:\Bbook.xlsに置いて他のテスト用ブックの標準Moduleに
Option Explicit

Sub Bopen()
　　Workbooks.Open Filename:="C:\Bbook.xls"
End Sub

Sub Bclose()
　　With Workbooks("Bbook.xls")
　　　　.Sheets(1).Unprotect
　　　　.Close True
　　End With
End Sub

>#BeforeCloseイベント内で保護シートの内容を書き換えるという事は、その後にSaveメソッドで保存しているはず。
この推測が外れてたらゴメンなさい。
どんな場合であっても必ず保存するという仕様は無いような気もしてるので。

KenKen_SP · Answer

解決済みかもしれないけど・・

回答者サイドで再現するためにＡブック、Ｂブックの最小限のソースを
掲載した方が手っ取り早いと思いますよ。

Wendy02 · Answer

#5 の回答者です。
内容にミスがありました。

×だったら、一旦、Book の該当シートをUnProtect してから、Protect , UserInterfaceOnlyにしなければなりません。

O>だったら、ThisWorkbook.Open イベントで、再度、該当シートを、Protect , UserInterfaceOnlyにしなければなりません。
Excel VBAのヘルプより
「引数 UserInterfaceOnly に True を設定した Protect メソッドをブックのワークシートに適用した場合、保存して閉じた後でもう一度開いたブックに対しては、画面上からもマクロからも変更ができなくなります。」

ただし、Excel 2007 の保護の設定内容によっては、挙動の違うものが存在します。

end-u · Answer

>・手動でシートを解除することがある
>・ブックB単独で使うことがある
>という条件から、このような仕様になっています。
この条件『のみ』ですか？
私が想像したのは『ブックBは作業中、常にProtect userinterfaceonly:=False でマクロによる変更も許さない』
という仕様なのかな、という事でした。
そうではなく、ユーザー操作による変更のみ制限したいなら
ブックBは

'ThisWorkbookModule
Option Explicit

Private Sub Workbook_Open()
　　Sheets(1).Protect userinterfaceonly:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
　　'終了前処理
　　Sheets(1).Cells(1).Value = Now() '例
End Sub

これで良い訳です。



ついでですが
>>あるいはブックBを閉じるマクロ内でProtect userinterfaceonly:=True を発行するとか。
>この方法が理想的なんですが、前述の条件があるのでちょっと複雑になりそうですね。。（検討の余地はあります）
ブックBのWorkbook_BeforeCloseイベントはそのまま
他ブックのマクロで
Sub test()
　　With Workbooks("ブックB.xls")
　　　　.Sheets(1).Protect userinterfaceonly:=True '■
　　　　.Close
　　End With
End Sub
とするだけです。

Wendy02 · Answer

こんにちは。

何か、思い違いがあるのではありませんか？

>Workbook_BeforeCloseイベント内で.Protect userinterfaceonly:=True

これは意味ありません。Close の前に、UserInterFaceOnly したところで、その後に、作業するならわかるけれども、終了したら、UserInterFaceOnlyのモードは残りません。

>あるブックAからブックBをOpenし、
>UserInterfaceOnlyでロックしたものを編集しようとするのですが、

だったら、一旦、Book の該当シートをUnProtect してから、Protect , UserInterfaceOnlyにしなければなりません。

end-u · Answer

>因みに、B.Closeを実行した中でのWorkbook_BeforeClose関数内での動作です。
ちょっとよくわからないんですが、
ブックBのWorkbook_BeforeCloseイベント内で.Protect userinterfaceonly:=True を発行しているという意味ですか？
もしそうなら、他マクロ実行で走ったWorkbook_BeforeCloseイベント内では Protectメソッド やUnprotectメソッドが効きません。

イベントプロシージャの場合、この『他マクロから呼び出される時』うまくいかない事例が多いです。
イベント自体は発生し、エラーなくコードも通るが、ある種のステートメントが無視される...といった感じです。
『メニュー コマンドが、BeforeSave イベントで動作しないプログラムで Excel でブックを保存と』
http://support.microsoft.com/kb/898511/ja
『XL2000: プログラムの優先 Workbook_Activate イベントが起動しません。』
http://support.microsoft.com/kb/294810/ja

普通はClose時にProtect userinterfaceonly:=Trueを発行する事はないんじゃないかと思いますが
仕様上、必要なんでしょうか。（ブックBのWorkbook_Openイベントではダメなのか）
どうしても今のままの仕様であれば、姑息な方法ですが ブックBを閉じるマクロを
　　Workbooks("ブックB.xls").Activate
　　Application.CommandBars.FindControl(ID:=106).accDoDefaultAction
などとして手動操作をエミュレートするようなマクロを実行すれば、
出来なくもない...といった所でしょうか。
あるいはブックBを閉じるマクロ内でProtect userinterfaceonly:=True を発行するとか。



もし、.Protect userinterfaceonly:=True がブックBのAuto_Openプロシージャに書かれている事が原因だったら
ブックBを開いた時に
.RunAutoMacros xlAutoOpen
RunAutoMacrosメソッドを実行するか、Workbook_Openイベントに書き換えれば良いです。

【Excel】他ブックからOpenされたブックはUserInterfaceOnlyが効かない？

再現性？

#6の回答者です。

何度もすみません。

この回答への補足

ぅあ、全然ダメだ。

>正規ユーザ：手動解除→保存→終了 のあとに

解決済みかもしれないけど・・

この回答への補足

#5 の回答者です。

この回答への補足

>・手動でシートを解除することがある

この回答への補足

こんにちは。

>因みに、B.Closeを実行した中でのWorkbook_BeforeClose関数内での動作です。

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

>正規ユーザ：手動解除→保存→終了　のあとに