エクセルのセルの編集制限（保護）の仕方

質問

いつもお世話になっております。
エクセル2016で、範囲を設定して、操作（並べ替え等）を指定して制限することができるのですが、入力するセルにはロックは当然かけらません。
しかし多人数で同じシートを使用する場合の保護として、データ入力後いったん保存したら、既存の入力セルの編集、上書きを禁止するような制限のかけ方はないのでしょうか？

回答No.8

HohoPapa · ベストアンサー

1.明示的に保存しないと機能しない点

空欄のセルに値を埋め、右上の×で閉じ
促してくる画面で保存した場合、
値を埋めたセルが保護されない動きになっている
ということと思います。

これについてはごめんなさい。
私に誤解がありました。

ソースコードの冒頭を

変更前
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
変更後
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

としてください。

2.シート保護に使うパスワードについて

保護はするものの、パスワードは設定しない。
という使い方をするのであれば、

変更前
Const MyPassword = "hoge" 'パスワード（省略可）
変更後
Const MyPassword = "" 'パスワード（省略可）

としてください。

3.最初の仕込み

課題ブックを開き、マクロをコピペするわけですが
課題シートは、保護されていない状態にしてください。
シートを保護する作業はマクロが担いますから。

4.マクロの保護作業に無関係なセルたちについて

>Const RowS = 16 'ロック範囲開始行
>Const RowE = 500 'ロック範囲終了行’
>Const ColS = 1 'ロック開始列
>Const ColE = 8 'ロック終了行
上記範囲外セルたちは、

手作業で、
シートの保護を解除し、
保護したい個所のセルはロック状態に、
保護したくない箇所のセルは非ロック状態にしてください。
その後、手作業であえてシートを保護する必要はありません。
シートを保護する作業はマクロが担いますから。

お礼

助かりました。
保護をかけるのはできていたのですが、毎日作業する多数のファイルで入力時の解除はともかく、終了時の設定を忘れてしまうことが多く、たまたま設定忘れのファイルが見つかり改善要求があったものです。

これをマニュアルに残して当該事項は完了です。
当面は別の２つのファイルにも設定して、そのあとは多数ある他のBookにも随時展開することになります。

本当にありがとうございました。

補足

お世話になります。
本当に何度もお手数をおかけします。
朝一で試してみたのですが、エラーメッセージがでて、デバックを見てみると下記の1行が黄色になり、やはりパスワード関連と思い、「Mypassuwod」を””に変えてみても・・・

.Unprotect Password:=MyPassword

まで書いてふと従来の制限を解除していないことに気づき編集範囲の設定と保護設定を解除してみたらバッチリできました。

本当に何度もご丁寧なご指導に感謝！！！

回答No.7

HohoPapa

>さて、時間ができたので早速下記をThisWorkbookにコピペしてみたのですが
>全くなにも起こらず、
>従来かけていた1-15行目だけが保護されています。

マクロをコピペしただけでは、何も起きません。
いったん上書き保存して閉じ、
開きなおしてください。

Ａさんが開くと、
マクロで指定したシート、指定したセル範囲の内、
その時に空欄だったセルだけが入力、変更、セル値の削除が可能です。
その後、Ａさんが保存します。
Ａさん、または、Ｂさんが再び開くと
その時に空欄だったセルだけが入力、変更、セル値の削除が可能
（Ａさんが新たに埋めたセルも保護された状態）
という動作です。

補足

何度もすいません。
原因がわかりました。が解決方がわかりません。

コード（行数、列数や従来保護の解除等）を何回か変えた時には毎回一旦閉じて、再度開いていたのですが、肝心の上書きをしていませんでした。
通常は何かいじると「変更を保存しますか？」と聞いてくるのでその時に「はい」で終了させているのですが、マクロの編集だけでは保存を聞いてこないので何事もなく終わって、開いただけでした。

ご指摘ではたと思い当って、上書きして見たらマクロのエラーメッセージが「パスワードが違っています」と出て来ました。

おそらくパスワードは「hoge]だと思うのですが、どこに入力するのかがわかりません。
また、出来ればパスワード不要のほうが良いのですが。

ところで、今までの保護と範囲許可の設定はどうしたらよいのでしょう？
解除不要でマクロ優先でしょうか？

当方のやりたいことは上記ご回答でぴったりで、何とかできそうな気がしますのでもう少しお付き合いお願いします。

回答No.6

HohoPapa

VBAを使わない（使えない）のであれば、
>多人数で同じシートを使用する
という皆さんがそれぞれ、
入力する都度、入力したセルをロックする必要があります。

保護の機能は既に使っているとのことですので、
マクロでは、あらかじめ指定した範囲について
期待の保護が動作するようにしてみました。

なお、マクロの配置先は、添付画像のとおり、
workbookです。
また、
ソースコードにシート保護のパスワードを書き込むことになりますので
ソースコードを利用者が覗けないようにする必要があります。

よかったら挑戦し、わからなければ聞いてください。

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

　Const RowS = 1　　　　　　　'ロック範囲開始行
　Const RowE = 7　　　　　　　'ロック範囲終了行’
　Const ColS = 1　　　　　　　'ロック開始列
　Const ColE = 8　　　　　　　'ロック終了行
　Const MyPassword = "hoge"　 'パスワード
　Const MySheet = "Sheet1"　　'保護したいシート名
　Dim RowCnt As Long
　Dim ColCnt As Long

　With ThisWorkbook.Sheets(MySheet)
　　.Unprotect Password:=MyPassword
　　For RowCnt = RowS To RowE
　　　For ColCnt = ColS To ColE
　　　　If .Cells(RowCnt, ColCnt).Value <> "" Then
　　　　　.Cells(RowCnt, ColCnt).Locked = True
　　　　Else
　　　　　.Cells(RowCnt, ColCnt).Locked = False
　　　　End If
　　　Next ColCnt
　　Next RowCnt
　　.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
　　　 Password:=MyPassword
　End With

End Sub

お礼

HohoPapaさん
お手数をお掛けしております。

さて、時間ができたので早速下記をThisWorkbookにコピペしてみたのですが全くなにも起こらず、従来かけていた1-15行目だけが保護されています。

入力範囲は16行目以降500行目まで、列はAからHまでの8列です。
パスワードも聞いてきませんでした。
保護したいシート名は「入力表」です。
上の左窓には「Workbook」右窓には「AfterSave]と表示されています。

おそらく基本的なところだと思うのですが、これが当方の実力ですので宜しくお願いいたします。

このマクロを使う場合、元の保護設定は全て外すのでしょうか？
一応従来設定していた、「シートの保護」の設定と「入力許可範囲」の設定は解除してみたのですが同じでしたが。。。

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

Const RowS = 16 'ロック範囲開始行
Const RowE = 500 'ロック範囲終了行’
Const ColS = 1 'ロック開始列
Const ColE = 8 'ロック終了行
Const MyPassword = "hoge" 'パスワード
Const MySheet = "入力表" '保護したいシート名
Dim RowCnt As Long
Dim ColCnt As Long

With ThisWorkbook.Sheets(MySheet)
.Unprotect Password:=MyPassword
For RowCnt = RowS To RowE
For ColCnt = ColS To ColE
If .Cells(RowCnt, ColCnt).Value <> "" Then
.Cells(RowCnt, ColCnt).Locked = True
Else
.Cells(RowCnt, ColCnt).Locked = False
End If
Next ColCnt
Next RowCnt
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
Password:=MyPassword
End With

End Sub

補足

いや～ご丁寧な回答痛み入ります。
必ずチャレンジしてみますが、今苦情対応で手が回らないので後日必ず試行し、結果報告（どうしても駄目そうならGive Uｐ宣言）しますのでしばらく時間をください）

おそらく再度の質問になると思います。
勝手ですがしばし時間をください。

先日顧客監査があり、入力場所以外（参照セルや計算式等）は保護がかけてあるが、既入力データ（入力範囲で保護されていないセル）の意図しない変更に対する対策を指摘されたものです。

つまり悪意のあるデータの改ざんの防止ではなく、ヒューマンエラーの防止が目的です。
よって必要時には他の人もいじれるようにパスワードも周知のものを使います。

他の顧客のデータ管理にも横展開できますので非常に助かります。

回答No.5

HohoPapa

>多人数で同じシートを使用する
>入力するまではフリーで、
>保存するとすべての入力セルが自動で保護されるような

値の埋まった、あるいは、埋めたセルは保護し
値の埋まっていないセルは非保護にしたい。
ということでね？

VBAで実現するのであれば
そのブックを保存する時（イベント）に
・シート保護を解除
・値の埋まったセルは全数ロックし、
・値の埋まっていないセルは全数ロックをオフに
・シート保護を設定
これらを行えば、期待の結果になりましょう。

そこでお聞きします。VBAは扱えますか？

補足

マクロの記録で出来たコードを少しいじるか、ここで教えてもらってコピペして、それを試行錯誤してどうにか使えるようにさるレベルですので実質出来ません。

回答No.4

imogasi

VBAですが、ブックのCLoseイベントに、シートを保護するコードを入れればすむのでは？
Googleで「vba Closeイベント 」で照会のこと。
ーー
シートを保護するコードがどうなるか？は
Googleで「vba シートの保護 」で照会すれば簡単にわかるのでは？
そこまでやって、思ったことと違うなら、その点に絞って、質問できると思う。
ーー
　仕事（他人と共同で使う、使わせる）でエクセルを使うなどの場合は、VBAができないと、手も足も出ないのでは？

お礼

早々のご回答ありがとうございます。

仰る通りだと思います。

回答No.3

chayamati

＞データ入力後いったん保存したら、
>既存の入力セルの編集、上書きを禁止するような制限のかけ方
-----------------------------------------------------------------------------------
全て自動ではないですが
作成したSheetに「シートの保護」を設定すると
全てのセルが変更できません
【校閲リボン】⇒【シートの保護】⇒【OK】
以上で全てが変更できなくなります。
「シートの保護」が「シート保護の解除」となります。
再度シートに修正を加えるにはこのをクリックします。
これなら修正は誰でも出来ますね
-------------------------
【校閲リボン】⇒【シートの保護】⇒【「パスワードを入力】⇒【OK】
とすると、パスワードを知らない人は「シート保護の解除」が出来んくなります。

お礼

ご回答ありがとうございます。

ご回答の保護はすでに多用しており、今回は少し違った方法ですので質問させてもらいました。

回答No.2

chayamati

校閲リボン下にシートの保護のツールがあります。
これをオンにすると全てのセルが入力できなくなります。
これは、全てのセルが保護になっているためです。
シートの保護を解除すると全てのセルが入力可になります
----------------------------------------------------------------
入力するセルのセルの書式設定の保護タグ内のロックの
(チェックボックス内の ✓)を外すと
ここだけはシートの保護をかけても入力可です

補足

早々のご回答ありがとうございます。
質問が言葉足らずでしたが、やりたいことは
入力するまではフリーで、保存するとすべての入力セルが自動で保護されるような制限のかけ方です。

＞データ入力後いったん保存したら、既存の入力セルの編集、上書きを禁止するような制限のかけ方

回答No.1

stf-chuta

シートの保護を使えば入力制限をかけられると思います。

https://support.office.com/ja-jp/article/%E4%BF%9D%E8%AD%B7%E3%81%95%E3%82%8C%E3%81%9F%E3%83%AF%E3%83%BC%E3%82%AF%E3%82%B7%E3%83%BC%E3%83%88%E3%81%A7%E7%89%B9%E5%AE%9A%E3%81%AE%E7%AF%84%E5%9B%B2%E3%82%92%E3%83%AD%E3%83%83%E3%82%AF%E3%81%BE%E3%81%9F%E3%81%AF%E3%83%AD%E3%83%83%E3%82%AF%E8%A7%A3%E9%99%A4%E3%81%99%E3%82%8B-75481b72-db8a-4267-8c43-042a5f2cd93a

補足

早々のご回答ありがとうございます。
質問が言葉足らずでしたが、やりたいことは
入力するまではフリーで、保存するとすべての入力セルが自動で保護されるような制限のかけ方です。

目的は他の人が入力したセルに次の人が意図しない変更、上書きの防止です。

＞データ入力後いったん保存したら、既存の入力セルの編集、上書きを禁止するような制限のかけ方・・・