[[20150325185258]]　『シートに保護をかけるとマクロが動かなくなりまし』（マクロ初心者）　ページの最後に飛ぶ

『シートに保護をかけるとマクロが動かなくなりました。』（マクロ初心者）

消されては困る数式が入ったセルがあるため、シートに保護をかけました。
そうすると一つのマクロだけが動かなくなりました。
シート1のG6に単語を入力すると、シート2（「リスト」）のリストから検索結果をH6に表示させるものです。

シートの保護をかけた後、上記の動作をするとエラーになりました。
「アプリケーション定義またはオブジェクト定義のエラーです」と表示され、
デバックをすると以下がハイライトされました。

rng0.Offset(, 1).Validation.Add Type:=xlValidateList, Formula1:=Join(Dic.Keys, ",")

マクロ初心者の為、何が原因なのか全くわかりません。
どなたかご教示いただけますようお願いいたします。

「シート1」
Private Sub Worksheet_Change(ByVal Target As Range)

Dim xrng As Range, rng As Range

Set xrng = Intersect(Target, Range("H6:H150"))

If xrng Is Nothing Then Exit Sub

With Application

.ScreenUpdating = False

.EnableEvents = False

For Each rng In xrng

rng.Offset(, 1).Validation.Delete

If rng.Value <> "" Then

Call ValidationTest(rng, Worksheets("リスト"))

End If

Next rng

.EnableEvents = True

.ScreenUpdating = True

End With

End Sub

－－－－－－－－－－－－－－－－－－－
「モジュール１」

Option Explicit

Sub ValidationTest(rng0 As Range, sh2 As Worksheet)

Dim Dic, rng As Range, x1stAdr As String
Dim buf As String
Set Dic = CreateObject("Scripting.Dictionary")
With sh2.UsedRange
Set rng = .Find(rng0.Value, LookIn:=xlValues, LookAt:=xlPart)
If Not rng Is Nothing Then
x1stAdr = rng.Address
Do
buf = rng.Value
If Not Dic.Exists(buf) Then
Dic.Add buf, buf
End If
Set rng = .FindNext(rng)
Loop While rng.Address <> x1stAdr
End If
End With
If Dic.Count > 0 Then
rng0.Offset(, 1).Validation.Add Type:=xlValidateList, Formula1:=Join(Dic.Keys, ",")　　←ここです
End If
Set Dic = Nothing
End Sub

＜使用 Excel：Excel2013、使用 OS：Windows8 ＞

 シート保護したセルを変更しようとしているからだと思いますが、
 ThisWorkbook の下に下記を書いて、EXCEL ファイルを開きなおしてどうでしょうか。
 Private Sub Workbook_Open()
    WorkSheets("リスト").Protect UserInterfaceOnly:=True
 End Sub

http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_030_040.html
（Mook） 2015/03/25(水) 19:28

 Mookさんから回答がありましたが、メモしましたので、蛇足ながら。

 シートの保護を掛けると、シート内の保護されたセルに対しては、手入力であれ、マクロからの書き込みであれ
 それはできなくなるのは承知されていますよね。

 ですから、rng0.Offset(, 1)　にあたるセルは、少なくとも、セルの保護をはずしておかなければ、
 入力規則リストから選択して、そのセルに値をいれること、そのものができなくなりますね。

 で、それは、ご存じで、シート上の、そのセル領域はセル保護がはずされているとします。

 でも、（もしかしたらＶＢＡのバグかもしれないなぁと思っているんですが）、保護がはずされたセルに対しても
 入力規則の削除（Delete)はできるんですが、Add　はできません。

 方法は、

 １．この処理をする前にシート保護をはずし、この処理が終わった時点でシート保護をかける。
 ２．あるいは、どこかで１回(Workbook_Openあたりで）、このシートに対して、UserInterfaceOnly:=True 付の
　　　シート保護をかけておく。そうすれば、マクロでさわる分には、自由自在になります。

 留意しなければいけないのは、１．　にしろ　２．にしろ、シート.Protect 実行時に、たくさんの引数に、適切な値を指定しなければいけないこと。
 操作で保護する際に、ユーザーに許可する操作を選びますよね。それらに対して、きちんと指定してやらないと
 ディフォルト値が採用されます。これが、たまたまシート保護を掛けたときに設定したものと同じであればいいのですが
 さもなければ不都合が発生します。

（β） 2015/03/25(水) 19:30

 UserInterfaceOnly付で、その時点の設定を継承してシート保護をかけるコードを書いてみました。
 ご参考まで。

 Sub Sample()
 '現在の保護要素を継承したシート保護 UserInterfaceOnly付
    Dim pp As Protection

    With Sheets("Sheet2")   '対象シート

        Set pp = .Protection

        .Protect DrawingObjects:=.ProtectDrawingObjects, _
                    Contents:=.ProtectContents, _
                    Scenarios:=.ProtectScenarios, _
                    AllowFormattingCells:=pp.AllowFormattingCells, _
                    AllowFormattingColumns:=pp.AllowFormattingColumns, _
                    AllowFormattingRows:=pp.AllowFormattingRows, _
                    AllowInsertingColumns:=pp.AllowInsertingColumns, _
                    AllowInsertingRows:=pp.AllowInsertingRows, _
                    AllowInsertingHyperlinks:=pp.AllowInsertingHyperlinks, _
                    AllowDeletingColumns:=pp.AllowDeletingColumns, _
                    AllowDeletingRows:=pp.AllowDeletingRows, _
                    AllowSorting:=pp.AllowSorting, _
                    AllowFiltering:=pp.AllowFiltering, _
                    AllowUsingPivotTables:=pp.AllowUsingPivotTables, _
                    UserInterfaceOnly:=True
    End With

 End Sub

（β） 2015/03/25(水) 22:14

ご返信ありがとございます。

私の説明が不足しておりましたが、シートの保護を設定したいのはシート1です。
他のシートだけにに保護をかけた場合、マクロは通常通り動作いたしました。

また、上記のマクロで指定しているセルの「ロック」は解除してあります。

＞Mookさんの教えてくださったマクロをコピーしましたが、
上記同様のエラーが出てしましました。。。

＞βさんのマクロはどこに追記したらよいのでしょうか？
　初歩的な質問で申し訳ございませんが、宜しくお願い致します。
（マクロ初心者） 2015/03/25(水) 22:57

 >また、上記のマクロで指定しているセルの「ロック」は解除してあります。

 コメントしましたように、セルがロックされていなくても、シート保護がかかっていれば入力規則のAddは、エラーになる仕様ですので。

 >Mookさんの教えてくださったマクロをコピーしましたが、 上記同様のエラーが出てしましました。。。

 エラーはでないはずです。実行していれば。
 ですから、一度保存して開き直して・・とMookさんからコメントありましたよね。
 コードをコピペして、開き直さないで有効にしたければ、VBE画面からWorkbook_Open マクロを【手動】で動かすこともできます。

 おそらく、どちらもやっていないでしょ？

 >βさんのマクロはどこに追記したらよいのでしょうか？

 同じく Workbook_Open です。
 ただ、もし、シート保護に際に、ダイアログの最初の２項目以外にチェックをつけていなければ
 Mookさんのコード、引数なしの Protect + UserInterfaceOnly だけで、問題はないですよ。
 もし、そのほかの項目もチェックをつけたりしているなら、βのコードが安心ですが。

（β） 2015/03/25(水) 23:12

コメント返信：

[ 一覧(最新更新順) ]

[[20150325185258]] 『シートに保護をかけるとマクロが動かなくなりまし』（マクロ初心者） ページの最後に飛ぶ

[[20150325185258]]　『シートに保護をかけるとマクロが動かなくなりまし』（マクロ初心者）　ページの最後に飛ぶ