保護されているシートでマクロ実行するとエラー

質問

Excel2010で勤務表を作っています。

A列にとある文字列（承認）と入力すると、その行が保護されるマクロを、下記URLからコピペして使わせて頂いてます。
http://questionbox.jp.msn.com/qa3277541.html

勤務表なので、土日祝日は網掛けになるよう条件付き書式を使っています。
休暇取得した場合は網掛けを付けて、休日出勤した場合には網掛けなしにしたり出来るようマクロをマクロの自動記録で作りました。

ところが、どこかの行が保護されている状態で、セルの網掛けを変更するマクロを実行すると
「実行時エラー'1004'アプリケーション定義またはオブジェクトの定義エラーです」と出てしまいます。
保護されている行ではなく、まだ保護はされていない行に実行しています。

そこで一旦保護の解除をしてから、セルの網掛けを変更するマクロを実行してみるとちゃんと動きます。
保護をかける時に、網掛け（セルの書式設定）を許可すれば動くのかな？とか思ったのですが、どこにどういう構文を入れたらいいのか、はたまたその認識すら間違っているのかも分かりません。

マクロに関してはド素人で、自動記録かWebで調べて見つけたマクロをちょっと加工して使う程度です。

どうか知恵をお貸しください。よろしくお願いします。

回答No.2

keithin · ベストアンサー

再掲：
＞ご自分で改造できない場合は，問題のマクロ（とアナタがいま実際に使ってる保
＞護マクロも）を実際のエクセルからコピーしてご質問に明示し，改めて別にご相
＞談を投稿してみてください。

ヒトの話きいてますか？

とりあえず「考えられる対応その１」で問題がクリアしたと言うことで，なによりです。
その２はトラブルの詳細が不明のまま「考えられる内容」を回答したので，ハズレだったようです。
その３は，今後の課題と言うことで頑張ってみてください。

最初のご相談では「網掛け」としかありませんでしたが，実際には「色々あれもこれもイッパイしたかった」中で，一体どの部分を自動処理にして（＝シートモジュールに書く「イベントプロシジャ」で処理させる），どの部分はそれ単独でマクロにする（＝標準モジュールに sub macro1()　みたいにして書く）のか，あなたが使いやすいやり方を考えて仕事を振り分けてください。

お礼

大変失礼を致しました。
今後気をつけます。
色々とご教示ありがとうございました。

補足

質問を再掲しました。
http://questionbox.jp.msn.com/qa6971639.html

回答No.1

keithin

「シート保護(セルのロック)を制御するマクロ」と「網掛けマクロ」の２つが出てきますが，肝心の，実際にエラーを起こす「網掛けマクロ」がどんなマクロなのか説明が抜けてますね。
ま，とりあえず。。

考えられる方法１：
いったんシートの保護を解除する
Ctrl+Aでシートの全てのセルの隅々まで選択し，セルの書式設定の保護のロックを外しておく
改めて問題のマクロを作用させてみる。

考えられる方法２：
「保護するマクロ」を次のように変更してみる
private sub worksheet_change(byval Target as excel.range)
　dim h as range
　activesheet.protect userinterfaceonly:=true
　set h = application.intersect(target, range("A:A"))
　if h is nothing then exit sub
　h.entirerow.locked = (h.cells(1) <> "")
end sub

いちどどこかのセルに記入して保護マクロを走らせてから，改めて問題の網掛けマクロを使ってみる。

考えられる方法３：
実際には今ナイショの「休暇/休日出勤の網掛け変更」マクロも，上述のworksheet_changeのマクロの中に組み込んだ方が，たぶん話はもっと簡単になります。
ご自分で改造できない場合は，問題のマクロ（とアナタがいま実際に使ってる保護マクロも）を実際のエクセルからコピーしてご質問に明示し，改めて別にご相談を投稿してみてください。

補足

ご回答ありがとうございます。
説明不足で申し訳ありませんでした。
網掛けマクロは全部で4つ作りました。（コレも多分スマートなやり方ではないと思いますが）

１）休日出勤した際に条件付き書式をクリアして網掛けなしにするマクロです。
Sub 休日出勤()
' 条件書式クリア
Selection.FormatConditions.Delete
End Sub

２）平日休んだ日に網掛けをするマクロです。
Sub 休日()

' 網掛け

With Selection.Interior
.ColorIndex = 0
.Pattern = xlGray16
.PatternColorIndex = xlAutomatic
End With
End Sub

３）２）のマクロで休日にしたけど、やっぱり出勤したという時に、１）だと網掛けなしにならなかったので、網掛けなしにするマクロを作りました。
Sub網掛けなし()

' 網掛けなし

With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

４）ごちゃごちゃいじってしまって最初の状態に戻したいと思ったので条件付き書式を再設定するマクロを作りました。が、２）の休日マクロを実行したセルは元に戻らないので仕方なく３）の網掛けなしマクロを実行しなければなりません。

Sub 書式クリア()

' 条件書式再設定

Range("A6:K36").Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAY($B6,2)>=6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray16
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(WEEKDAY($B6)=1,COUNTIF(祝日,$B6))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlGray16
.PatternColorIndex = xlAutomatic
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

※１）から３）は網掛けしたいところ、網掛けなしにしたいところを範囲選択してから実行しなければなりません。

その他、保護を解除する際にパスワード認証が欲しかったので、「保護解除」ボタンを押すためにパスワード認証させるマクロもあります。
これは特に問題なく動いています。

５）パスワード認証つき保護解除マクロ
Sub password()
Dim pw As Long
pw = Application.InputBox( _
prompt:="パスワード入力", Type:=1)
If pw <> "123" Then
MsgBox "パスワードが違います"
Exit Sub
Else
MsgBox "保護解除しました"
ActiveSheet.Unprotect

End If
End Sub

以下はkeithinさんの方法を試してみた結果です。

考えられる方法１：
保護のロックを外した状態ですと、網掛けマクロは問題なく動作しました。

考えられる方法２：
１）の休日出勤マクロを実行すると実行時エラーになりました。
Selection.FormatConditions.Delete

２）と３）は問題なく動きました。
４）もエラーになりました。
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAY($B6,2)>=6"

考えられる方法３：
「休暇/休日出勤の網掛け変更」マクロも組み込みたいと思ってます。

ちなみに、本当に初歩的な質問で申し訳ないのですが、１）から５）のマクロが記述されている場所がそれぞれ違いますが、これも問題の一つなのでしょうか？
１）と５）はMicrosoft Excel Objectsの下のSheet1(勤務表)にあり
２）３）４）は標準モジュールのModule1に記述されています。

長々と失礼しました。