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

質問

標記のとおりの質問です。

あるブックAからブックBをOpenし、
UserInterfaceOnlyでロックしたものを編集しようとするのですが、
その際に

実行時エラー '1004':
変更しようとしているセルは保護されているため、読み取り専用となっています。

とのエラーが出てしまいます。
これはブックBで単独で実行するときには出ない症状です。
他ブックから開かれたブックはUserInterfaceOnlyが作用していないのでしょうか。
因みに、B.Closeを実行した中でのWorkbook_BeforeClose関数内での動作です。

どなたかご教示ください。よろしくお願いいたします。

回答No.12

end-u · ベストアンサー

再現性？
【Ｑ】
'"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でも。

>このエラー・メッセージが正確なものかはわかりませんが、ご質問者さんのコードもみないで、回答者が一方的な想像で、空回りしているような気がします。どの時点で、エラーが発生しているのかは、回答者側では確認できていません。
どうも。スルドいご指摘、ありがとうございます。

お礼

ご回答ありがとうございます。

あれから仕様の変更が起こってしまい、
この部分になかなか手を付けられなくなってしまいました。
長期間放置するのも良くないので、一旦締めさせていただきます。
私の拙い説明を元に色々と紐解いていただき、感謝しております。
落ち着いたらまたじっくりやってみたいと思います。
本当にありがとうございました。

回答No.11

Wendy02

#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

このまま、あれこれ書いても、こちらが再現性が得られないのでは、のれんに腕押しのようで、ちょっと、これ以上は回答は難しいなって思います。

お礼

ご回答ありがとうございます。

あれから仕様の変更が起こってしまい、
この部分になかなか手を付けられなくなってしまいました。
長期間放置するのも良くないので、一旦締めさせていただきます。
私の拙い説明を元に色々と紐解いていただき、感謝しております。
落ち着いたらまたじっくりやってみたいと思います。
本当にありがとうございました。

回答No.10

end-u

何度もすみません。
>'標準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さんの各種の提案について検討しきれておりません。
明日(9/7)は時間が取れそうなので、試してみたいと思います。

>http://homepage2.nifty.com/kmado/kvba.htm​
>E00M090[マクロを無効にする]で開くと使えないxlsファイル
>（ここ参考にしてみてください）
こんな方法があったんですね。。
もっとよく調べておけばよかったです。

回答No.9

end-u

ぅあ、全然ダメだ。ゴメンなさい。
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
の方がまだましかも。

回答No.8

end-u

>正規ユーザ：手動解除→保存→終了　のあとに
>非正規ユーザ：ファイルを開く→マクロを実行しない
>とするとプロテクトが外れたままなのがネックとなります。
『非正規ユーザ：ファイルを開く→マクロを実行しない』これは、マクロ無効で開くという意味ですね？
正規ユーザーがマクロ無効で開くという想定は必要ないという事ですかね。
正規ユーザー：マクロ無効で開く→手動解除→保存→終了
この場合、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メソッドで保存しているはず。
この推測が外れてたらゴメンなさい。
どんな場合であっても必ず保存するという仕様は無いような気もしてるので。

回答No.7

KenKen_SP

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

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

補足

遅くなってすみません。ご回答ありがとうございます。

>最小限のソースを掲載した方が手っ取り早いと思いますよ。
そうですね。どこまでが最小限になるか見極めて、可能なら載せようと思います。

回答No.6

Wendy02

#5 の回答者です。
内容にミスがありました。

×だったら、一旦、Book の該当シートをUnProtect してから、Protect , UserInterfaceOnlyにしなければなりません。

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

ただし、Excel 2007 の保護の設定内容によっては、挙動の違うものが存在します。

補足

ご回答ありがとうございます。
下記と併せて回答致します。

>Close の前に、UserInterFaceOnly したところで、その後に、作業するならわかるけれども、終了したら、UserInterFaceOnlyのモードは残りません。
諸所で行う一連のProtect作業を関数化しているため、UserInterfaceOnlyが入っているだけです。
意味はありませんが、ユーザに対するProtectの効果は残るのでそのままにしています。
紛らわしくてすみません。

>>あるブックAからブックBをOpenし、
>>UserInterfaceOnlyでロックしたものを編集しようとするのですが、
>だったら、ThisWorkbook.Open イベントで、再度、該当シートを、Protect , UserInterfaceOnlyにしなければなりません。
それは行っています。
UserInterfaceOnlyの特性についても承知しております。
その部分ではエラーも出ていませんし、
今回の問題はブックAから実行した場合にのみ起こります。
編集という言い方がまずかったと思いますが、これはマクロから行っています。
Excelのバージョンは2003なので挙動に関しては想定内のはずです。

回答No.5

end-u

>・手動でシートを解除することがある
>・ブック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
とするだけです。

補足

確かにとても良い方法なのですが、
セキュリティ上どんなときでも他者に対してプロテクトが外れないようにしたいのです。
ブックAは管理者のみが実行でき、ブックBは特定の部署だけで使います。
(ブックBは各部署に対して存在します(B1,B2,B3,...))
ただし、ブックAは管理者しかアクセスできないのに対し、ブックBは全ての部署がアクセス可能です。

Workbook_Openのみだと、ブックB単独で使った場合
正規ユーザ：手動解除→保存→終了　のあとに
非正規ユーザ：ファイルを開く→マクロを実行しない
とするとプロテクトが外れたままなのがネックとなります。

本当に鋭い指摘で舌を巻くばかりです。
後出しが良くないのは解っているつもりなのですが、
ここまで話がこじれるとは思わなかったもので・・・
懇切に教えていただいているのに、大変すみません。

回答No.4

Wendy02

こんにちは。

何か、思い違いがあるのではありませんか？

>Workbook_BeforeCloseイベント内で.Protect userinterfaceonly:=True

これは意味ありません。Close の前に、UserInterFaceOnly したところで、その後に、作業するならわかるけれども、終了したら、UserInterFaceOnlyのモードは残りません。

>あるブックAからブックBをOpenし、
>UserInterfaceOnlyでロックしたものを編集しようとするのですが、

だったら、一旦、Book の該当シートをUnProtect してから、Protect , UserInterfaceOnlyにしなければなりません。

回答No.3

end-u

>因みに、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イベントに書き換えれば良いです。

補足

ご回答ありがとうございます。

>ブックBのWorkbook_BeforeCloseイベント内で.Protect userinterfaceonly:=True を発行しているという意味ですか？
その通りです。その場合は効かないんですね・・・

>普通はClose時にProtect userinterfaceonly:=Trueを発行する事はないんじゃないかと思いますが
>仕様上、必要なんでしょうか。（ブックBのWorkbook_Openイベントではダメなのか）
・手動でシートを解除することがある
・ブックB単独で使うことがある
という条件から、このような仕様になっています。

>あるいはブックBを閉じるマクロ内でProtect userinterfaceonly:=True を発行するとか。
この方法が理想的なんですが、前述の条件があるのでちょっと複雑になりそうですね。。（検討の余地はあります）

提示していただいた他の方法でも試してみようと思います。

回答No.2

sykt1217

読み取り専用を解除してみてもダメですかね？

Workbooks.Open Filename:=ファイル名 & ".xls", ReadOnly:=False

補足

ご回答ありがとうございます。
試してみましたが、残念ながらうまくいきませんでした。