EXCEL VBA 特定セルの保護解除したい

質問

お世話になります。田中ともうします。

EXCELのあるシートを一度全て保護して、特定セルの解除をVBAで実現したいのです。
現在、上記処理を毎回手動でやっており、手間もそうですが間違いも多くなり困っています。
イメージとしましては[保護]というボタンをクリックしたら上記処理がVBAで実行されるようにしたいのです。どなたかご教授いただけませんでしょうか。

以下処理の順番イメージです。

■全てのセルをロック

■保護解除したいセル その(1)
行は決まっていますが列(XX)は可変です。
XX７行目に[調整]という文字がありますのでそのXX列の、XX8行目、XX10行目、XX14行目、XX16行目、XX18行目、XX28行目が保護解除したいセルになります。

■保護解除したいセル その(2)
D20を起点にして、F20、H20、J20・・と2列ずつ右に移動して保護解除したいのです。
ある程度右いにいくと"E"という文字にあるので、この"E"にぶつかるまで上記のように2列ずつ解除したいのです。

■保護解除したいセル その(3)
D24を起点にして、F24、H2４、J2４・・と2列ずつ解除したいのです。
条件はその(2)と同じです。

これを毎回手でやっているため結構大変で困っています。
どなたかお助けいただけませんでしょうか？

よろしくお願い致します。

EXCEL2013

回答No.2

real beatin（@realbeatin） · ベストアンサー

こんにちは。

sheet.Protect メソッド の 名前付き引数 UserInterfaceOnly の扱いについては、
ＶＢＡのヘルプ等で、確認してください。
一度 UserInterfaceOnly:=True を設定しておけば、ブックを閉じるまでの間、
シート上でのユーザー操作についてはロックされたものを保護しますが、
ＶＢＡからの処理については、これを許容して保護を適用しません。
UserInterfaceOnly:=True を設定した状態でブックを保存した場合、
次にそのブックを開いた場合は通常の保護と同じ状態になります。
一般的には、ブックを開いたタイミングで、
UserInterfaceOnly:=True を設定するマクロを実行するように設定しておいて、
sheet.Unprotect メソッドを頻繁に実行するような煩雑さを回避するように
使います。
sheet.Unprotect → sheet.Protect のように２回処理が必要なところを、
sheet.Protect UserInterfaceOnly:=True のように１回で済ませられるだけでも
扱うメリットはありますから、この機会に、ＶＢＡヘルプ等々を確認した上で、
覚えてみて下さい。

（＃尚、回答No.１様が唱える異論、については私には理解できません。
　私自身は仕事上の経験として、ご質問と同様のニーズに添ったマクロ
　を提供したこともありますし、それが何故必要なのかも理解しているつもりです。
　加えて、質問者さんが提示されている設計自体は、完成度の高い、適切なものであると
　私は考えますので、
　回答の内容としては回答No.１とNo.２では相容れない対称的なものになっています。）

以下、細かい点で、こちらが適当に仕様を選定し補って書いています。
そちらで、指定（または確認）するべきポイントを★印でマークしています。
それでも、うまく行かないことがあったり、疑問が残った場合は、
補足欄にでも詳しく書き込んでみて下さい。

' ' ///

Sub Re8977659()
Dim Target As Range
　　With Sheets("Sheet1")　'　★シート名を正しく指定する
　　' ' シートの保護を　ＶＢＡからの操作を除外する形で再設定。
　　　　.Protect UserInterfaceOnly:=True　'　★Password 他、必要な引数がある場合は自分で指定すること

' ' ■全てのセルをロック
　　　　.Cells.Locked = True

' ' ■保護解除したいセル その(1)
　　　　With .Range("7:7")
　　　　' ' XX７行目に[調整]という文字がありますのでそのXX列 ... < "[調整]"という文字列値であるセルを検索
　　　　　　Set Target = .Find(What:="[調整]", After:=.Cells(.Cells.Count), _
　　　　　　　　　　　　　　LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
　　　　End With

　　　　If Not Target Is Nothing Then
　　　　' ' ...そのXX列の、XX8行目、XX10行目、XX14行目、XX16行目、XX18行目、XX28行目が保護解除したいセル ...
　　　　　　Target.EntireColumn.Range("A8,A10,A14,A16,A18,A28").Locked = False
　　　　End If

' ' ■保護解除したいセル その(2) ■保護解除したいセル その(3)
Dim arr, v, i As Long
　　　　arr = Array(20, 24)　'　その(2) その(3)　の行位置を配列で指定

　　　　For Each v In arr　'　その(2) その(3)　の行位置を総当たりでループ
　　　　' ' ある程度右*にいくと"E"という文字にあるので、この"E"にぶつかる ... セルを検索
　　　　　　With .Rows(v).Range("D1:XFD1")
　　　　　　　　Set Target = .Find(What:="E", After:=.Cells(.Cells.Count), _
　　　　　　　　　　　　　　LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
　　　　　　End With

　　　　　　If Not Target Is Nothing Then
　　　　　　' ' (2) D20を起点にして、F20、H20、J20・・と .... この"E"にぶつかる【手前】まで... 2列ずつ右に移動して保護解除したいのです。
　　　　　　' ' (3) D24を起点にして、F24、H2４、J2４・・と2列ずつ解除したいのです。条件はその(2)と同じです。
　　　　　　　　For i = 4 To Target.Column - 1 Step 2　'　★もしも"E"のセルを【含める】場合は左の記述中の -1 を削除
　　　　　　　　　　.Cells(v, i).Locked = False
　　　　　　　　Next i
　　　　　　End If
　　　　Next

　　End With

End Sub

お礼

realbeatin さん、ありがとうございました！！

回答No.5

kagakusuki

取り敢えずVBAも作ってみました。
　尚、下記のVBAでは、「保護解除したいセル その(2)」が存在する列と「保護解除したいセル その(3)」が存在する列は、どちらも同じ列(最終列は、20行目も24行目も同じ列)であるものとして処理を行う様になっておりますので、もし20行目の最終列の列数と、24行目の最終列の列数が異なっている事があり得る場合には、その旨を御知らせ願います。

Sub QNo8977659_EXCEL_VBA_特定セルの保護解除したい()

Dim myRange As Object

Cells.Locked = True 'シート上の全てのセルをロック
Set myRange = RowS(7).Find(what:="調整", LookIn:=xlValues, lookat:=xlWhole) '7行目において「調整」と入力されているセルを検索

'7行目において「調整」と入力されているセルが存在している列の8,10,14,16,18,28行目のセルのロックを解除
If Not myRange Is Nothing Then _
Intersect(Columns(myRange.Column), _
Range("8:8,10:10,14:14,16:16,18:18,28:28")).Locked = False

'20行目においてF列以降に「E」と入力されているセルを検索
Set myRange = Range(Range("F20"), Cells(20, Columns.Count)) _
.Find(what:="E", lookat:=xlWhole, MatchByte:=False)

'20行目と24行目において、「E」と入力されているセル
If Not myRange Is Nothing Then
With Range("F20")
.Locked = False 'F20セルのロックを解除
.Offset(4).Locked = False 'F20セルの4行下のセルのロックを解除
.Resize(4, 2).Copy 'F20:G:24の範囲をコピー

'F20:「20行目において『E』と入力されているセルの4行下/1列右にあるセル」のセル範囲に、値のみ貼り付け
Range(.Offset(0, 2), myRange.Offset(4, 1)).PasteSpecial _
Paste:=xlPasteFormats
End With

Application.CutCopyMode = False 'コピーモード解除

End If

End Sub

お礼

kagakusukiさん、ご連絡ありがとうございます！
遅い時間にも関わらず何度もご対応いただきましてありがとうございました。

さきほど会社で使っていたEXCELで試しました結果、ご教授いただきました方法で実現できました！！！ありがとうございました！

ご指摘いただきました通り、もしかしますとVBAである必要のないのかもしれませんが練習のためにわざとVBAでやろうとしていました。^^;

細かなご説明+ロジックまでご提示いただきまして本当にありがとうございました！
とても参考になりました。

このたびは本当にありがとうございました!!

回答No.4

kagakusuki

>１）　【ロックされていないセル】については必要な編集が済んだという前提で、
> 　　　【ロックされているセル】へと設定を変更し、以後編集出来ないようにしたい
> そして、
> ２）　次に編集を加えるべき候補となるセル範囲を
　　　新たに【ロックされていないセル】へと設定を変更し、編集可能にする
> というニーズであった場合、
> 　　　一旦すべてのセルをへと設定を【ロック】してから、
> 　　　【ロックされないセル】を設定する
> という手順を選べば処理が簡潔なものになるであろう

　「一旦すべてのセルをへと設定を【ロック】」は、8:28の行範囲をまとめて選択してから[ロック]をONにすれば出来ます。

　又、「【ロックされないセル】を設定する」は
XX8セルのロックをOFF
　　↓
XX8:XX9のセル範囲をコピー
　　↓
XX8:XX28のセル範囲を範囲選択([Sheft]キーを押しながらXX28セルをクリック)
　　↓
20行目全体を選択
　　↓
Excelの検索機能(ショートカット:[Ctrl]キー+[F]キー)で"E"が入力されているセルを検索
　　↓
「"E"が入力されているセル」のロックをOFF
　　↓
「"E"が入力されているセル」をコピーして、その4行下のセル(同じ列の24行目のセル)に書式設定のみ貼り付け
　　↓
「"E"が入力されているセル」:「『"E"が入力されているセル』の右隣の列の24行目のセル」のセル範囲をまとめて範囲選択
　　↓
選択範囲をコピー
　　↓
名前ボックスにD20:「『"E"が入力されているセル』の右隣の列の24行目のセル」のセル範囲を入力するか
或いは
[Sheft]キーを押しながらD20セルをクリック
　　↓
選択範囲に書式設定のみ貼り付け

という操作を行えば済む事です。
　文章に記述しますと長くなりますが、十枚以上のシートに対して同じ操作を行うというのであれば兎も角、1枚(或いは少数の枚数)のシートに対して行うのであれば、さほど大変な作業になる訳でもない様な気が致します。
　勿論、VBAを使った方が多少は楽になりますが、VBAを組むための労力の事を考えますと、どちらが得なのか微妙な気が致します。

回答No.3

real beatin（@realbeatin）

回答No.２です。

> それが何故必要なのかも理解しているつもりです。
この表現だけでは、宜しくない態度であったと思いましたので補足させてください。

現在、保護されているシート上で、
【ロックされているセル】、と、【ロックされていないセル】、とに分かれていて、
編集できるセル範囲をユーザーに対して制限しているとします。
ここで、
１）　【ロックされていないセル】については必要な編集が済んだという前提で、
　　　【ロックされているセル】へと設定を変更し、以後編集出来ないようにしたい
そして、
２）　次に編集を加えるべき候補となるセル範囲を
　　　新たに【ロックされていないセル】へと設定を変更し、編集可能にする
というニーズであった場合、
　　　一旦すべてのセルをへと設定を【ロック】してから、
　　　【ロックされないセル】を設定する
という手順を選べば処理が簡潔なものになるであろう、ということ。
、、、以上が私が「理解しているつもりです」と書いた意味内容でした。
失礼しました。

回答No.1

kagakusuki

>これを毎回手でやっているため結構大変で困っています。

との事ですが、それは

>■全てのセルをロック

という操作を毎回行っている事が原因です。
　一旦、保護解除したいセルのロックを解除しておいて、後はロックを掛けない様にしておきさえすれば、シートに保護を掛けたとしてもロックはされずに済みますし、シートの保護を解除した後でも、各セル毎の「保護を掛けた際にロックをする」/「保護を掛けた際にロックをしない」の設定は変わらずに残ったままとなっているのですから、ロックを解除したいセルが毎回同じセルという事であれば、

>■全てのセルをロック

などという事を行う必要など無い筈です。

　従いまして、最初に一度手作業にてロックを解除しておきさえすれば、VBAなど使う必要もないと思われます。

　それとも、「保護を掛けた際にロックをする」/「保護を掛けた際にロックをしない」という設定を行うための方法を御存じないという事なのでしょうか?
　「保護を掛けた際にロックをしない」様にする際には、まず、その設定を行う対象のセル(或いはセル範囲)を選択して下さい。
　次に、選択されているセル(或いはセル範囲)にカーソルを合わせてマウスを右クリックして下さい。
　そうしますと幾つかの選択肢が現れますので、その中から[セルの書式設定]を選択してクリックして下さい。
　すると「セルの書式設定」ダイアログボックスが現れますので、その中の[保護]タブをクリックして下さい。
　そして現れた[ロック]という項目のチェックを外してから、「セルの書式設定」ダイアログボックスの[OK]ボタンをクリックして下さい。
　これで少なくとも1つのセルに対して「保護を掛けた際にロックをしない」という設定がなされますが、複数のセル範囲に対して「保護を掛けた際にロックをしない」様に設定する際には、設定済みのセルの1つをコピーして、「保護を掛けた際にロックをしない」設定にしたいその他のセルに、「書式のみ貼り付け」を行う事で、書式と共に「保護を掛けた際にロックをする」/「保護を掛けた際にロックをしない」という設定も貼り付ける事が出来ます。

　又、毎回新しいシートに対して同じ操作を行う様な場合には、まず、「保護を掛けた際にロックをする」/「保護を掛けた際にロックをしない」という設定と、各種の書式を設定し終えたシートを雛型のシートとしてどこかに保存しておき、御質問の件の作業を行う際には、その雛型のシートの全てのセル範囲をコピーして、作業の対象としているシートに書式のみを貼り付ければ良いと思います。