[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シートに保護をかけるとマクロが動かなくなりました。』(マクロ初心者)
消されては困る数式が入ったセルがあるため、シートに保護をかけました。
そうすると一つのマクロだけが動かなくなりました。
シート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
−−−−−−−−−−−−−−−−−−−
「モジュール1」
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) にあたるセルは、少なくとも、セルの保護をはずしておかなければ、 入力規則リストから選択して、そのセルに値をいれること、そのものができなくなりますね。
で、それは、ご存じで、シート上の、そのセル領域はセル保護がはずされているとします。
でも、(もしかしたらVBAのバグかもしれないなぁと思っているんですが)、保護がはずされたセルに対しても 入力規則の削除(Delete)はできるんですが、Add はできません。
方法は、
1.この処理をする前にシート保護をはずし、この処理が終わった時点でシート保護をかける。 2.あるいは、どこかで1回(Workbook_Openあたりで)、このシートに対して、UserInterfaceOnly:=True 付の シート保護をかけておく。そうすれば、マクロでさわる分には、自由自在になります。
留意しなければいけないのは、1. にしろ 2.にしろ、シート.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 です。 ただ、もし、シート保護に際に、ダイアログの最初の2項目以外にチェックをつけていなければ Mookさんのコード、引数なしの Protect + UserInterfaceOnly だけで、問題はないですよ。 もし、そのほかの項目もチェックをつけたりしているなら、βのコードが安心ですが。
(β) 2015/03/25(水) 23:12
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.