マクロ　実行エラー1004

Question

エクセル2000を使用しています。
以下のマクロを作り、19行目の”AAA”を探し、29行目で1つ前の列までロックしようとしています。

新規のエクセルで使用すると、動作しますが、肝心の組み込みたいエクセルだと動作しません。
19行目には”AAA”以外の文字列があります。
（19行目には、”AAA”は必ず1つしかありません）
また、”AAA”は関数で19行目に表示されています。

実行すると最後の行のRange(Range("A29"), h.Offset(1, -1)).Locked = Trueで黄色ハイライトされ
実行エラー1004で「RangeクラスのLockedプロパティを設定できません。」を表示されます。
何か対処するよい方法があれば、教えてください。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim h As Range
  Cells.Locked = False
  ActiveSheet.Protect userinterfaceonly:=True
  Set h = Range("19:1").Find(what:="AAA", LookIn:=xlValues, lookat:=xlWhole)
  If h Is Nothing Then Exit Sub
  If h.Column = 1 Then Exit Sub
  Range(Range("A29"), h.Offset(1, -1)).Locked = True

End Sub

ki-aaa · Accepted Answer

前回の回答の後、いろいろ調べました。

ブックを閉じますと、ActiveSheet.Protect userinterfaceonly:=True

のuserinterfaceonly:=Trueの効力が無くなり、

ActiveSheet.Protectだけが残ることが解りました。

でわ

keithin · Answer

ご相談で言葉で書かれた説明が正しいとすると，幾つか直し間違いがあります。
また，ご相談で書かれているエラーの症状も間違っているように思います。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim h As Range
　ActiveSheet.Protect userinterfaceonly:=True
　Cells.Locked = False
　Set h = Range("19:19").Find(what:="AAA", LookIn:=xlValues, lookat:=xlWhole)
　If h Is Nothing Then Exit Sub
　If h.Column = 1 Then Exit Sub
　Range(Range("A29"), h.Offset(10, -1)).Locked = True

End Sub

それと，19行には数式でAAAが計算されているとの事ですが，その計算が走る（AAAが計算される）ための参照元になるセルがこのシート内に存在しないと，今回のマクロは起動しないので無意味になります。
今回あなたが使おうとしたマクロは，19行目の計算式のセルがAAAになったら（あるいは別の列の19行のセルがAAAになったら）自動で動くのでは無い，事に留意してください。

Wendy02 · Answer

>　ActiveSheet.Protect UserInterFaceOnly:=True
通常は、Workbook_Open や Auto_Openを使います。

そして、シートを指定して、設定しますが、そもそも、今回の UserInterFaceOnly:=True は、マクロが裏で動くわけではありませんから、それは、不要だと思います。Change イベントと一緒に用いるのだったら、どこか、確実に、Locked していない所のセルが必要です。

でも、私には質問は、何をしたいのか目的が良く見えないですね。マクロにするための、情報の半分が足りないという感じです。マクロというものは、繰り返し行うということですから、今の状態では、質問の趣旨が見えてこないのです。

前回の質問を見ますと、これ自体は、特に問題はありません。
>1行目のG1に”ロック”という文字列が入っていたら
>2行目のG列の1つ前のF列で”A2：F2”まで、セルをロックしたいのですが、
（ただし、私の考えているコードはまったく違います）

しかし、今回の、
>19行目の”AAA”を探し、29行目で1つ前の列までロックしようとしています。
たぶん、任意の19行目という意味だと解釈しました。

例えば、G7 だったら、F7? ～A29 までをロックしようとすることですか？
もともと、"AAA"という発想が分かりません。一体、これは、どういう過程で入力しているのでしょうか？一回きりなら、いままでのコードでよいです。ただ、次にどうするか、ということです。このままでは、ロックした所は入力出来ませんから、Range("19:1").Find　とすれば、G7に"AAA"があったら、それ以降は、その次の行から29行目までは入力出来なくなります。入力範囲がどんどん狭まっていくはずです。

Change イベントで、
>　Cells.Locked = False

エラーが出る場合と、出ない場合があります。ただ、基本的には、Changeイベントというものは、Enter で発生するイベントですから、今の状態では、ロジックが成り立ちません。

意味のないマクロになりますが、一応、こんなマクロを書けるという証明のためにコードを提示させていただきます。そうしないと、小言にしか取れませんからね。
おそらく、もう一度、どうしたいのかまとめたほうがよいような気がします。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　Dim h As Range
　Me.Unprotect
　Set h = Rows("1:19").Find(What:="AAA", LookIn:=xlValues, LookAt:=xlWhole)
　If h Is Nothing Then Exit Sub
　If h.Column = 1 Then Exit Sub
　Set h = h.Offset(1, -1)
　Range(Cells(29, 1), h).Locked = True
　Me.Protect
End Sub

ki-aaa · Answer

シートを保護したまま、いろんな処理をしようとしています。

ActiveSheet.Unprotect を最初に書いてください。

なぜか、ブックを閉じないままだと、この命令は無くてもいいみたいです。

end-u · Answer

>新規のエクセルで使用すると、動作しますが、肝心の組み込みたいエクセルだと動作しません。
『肝心の組み込みたいエクセル』には結合セルがあるのではないですか？
そのシートでエラーが出る時、結合セルが
Range(Range("A29"), h.Offset(1, -1))の範囲を跨っている為エラーが出ているのでしょう。

Sub test()
　　With Sheets.Add
　　　　.Range("B2:C3").Merge
　　　　.Range("A1:B2").Locked = True
　　End With
End Sub
..こんな現象です。

シート仕様を見直して結合セルを使わないようにするのがベターですが、
使わざるをえない場合、
結合セル範囲をどう扱うかによって処理がわかれるかと思います。
上記testの例では、Lockするのはどの範囲が正解ですか？
A1:C3　でしょうか。それともA1:A2,B1,B2:C3　でしょうか。

A1:C3のように結合セルを含む最大範囲に拡張する場合、
　：
　Range("A29", h.Offset(1, -1)).Select
　Selection.Locked = True
　：
と、Selectionを利用すると良いかもしれません。

A1:A2,B1,B2:C3のように、基本はA1:B2範囲で、
結合セルの場合だけ拡張する場合はLoop処理になります。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim h As Range
　　Dim r As Range

With Me
　　　　.Cells.Locked = False
　　　　.Protect userinterfaceonly:=True
　　　　Set h = .Rows(19).Find(what:="AAA", LookIn:=xlValues, lookat:=xlWhole)
　　　　If h Is Nothing Then Exit Sub
　　　　If h.Column = 1 Then Exit Sub
　　　　For Each r In .Range("A29", h.Offset(1, -1))
　　　　　　r.MergeArea.Locked = True
　　　　Next
　　End With
End Sub
#広範囲だとちょっともたつきます。

マクロ 実行エラー1004

前回の回答の後、いろいろ調べました。

ご相談で言葉で書かれた説明が正しいとすると，幾つか直し間違いがあります。

> ActiveSheet.Protect UserInterFaceOnly:=True

シートを保護したまま、いろんな処理をしようとしています。

>新規のエクセルで使用すると、動作しますが、肝心の組み込みたいエクセルだと動作しません。

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

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

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

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

マクロ　実行エラー1004

>　ActiveSheet.Protect UserInterFaceOnly:=True