2020/01/29
2020/04/30 改訂（Protectメソッドに｢UserInterfaceOnly｣パラメータを設置する方法を追加）

セル値書き換えを、手入力は防止しＶＢＡのみで制御する




１．セルを保護する意味

Excel帳票などのデータ入力部分を作る時、インプットボックスやオプションボタンなどで入力させたり、カレンダーで選ばせたり　と作り手は工夫をします。その目的は「ユーザの操作性向上」や「データ型やデータ種別を統一して後処理を楽にする」などだと思います。
しかしその入力データを、ユーザはわざわざ編集モードにして、作り手の意図とは異なったデータに書き換えてしまうことがあります。
そこで今回は、手作業でのセル編集は禁止するが、マクロを通してだと編集可能となる方法について２つ紹介します。

２．書き換えても、元に戻してしまう方法

１つ目の方法ですが、「編集モードには入れるが、セルの値を書き換えると元に戻す」方法です。なお「値は変更しなくても編集モードに入った」だけでもイベントは発生しますので「編集モードに入る前の値に戻す」という表現が正しいかもしれません。
画面として図２－１のようなのを用意しました（一番下からファイルがダウンロードできます）。Ｂ２セルを対象のセルとし、Undoと表示があるボタンを押すと InputBox が出てきてＢ２セルの値を書き換える、というものです。手作業でＢ２セルを書き換えようとしても元に戻ってしまいます。
ほとんど意味不明のシステムですが、「入力値は、マクロで制御している」ことの代わりと思ってもらえれば助かります。

図２－１

このワークシート（Sheet1）に記載されているＶＢＡコードは以下の通りです。
  1. '------ ⇩（１）変数宣言部　-----------------------------------------------------------
  2. Dim vba_change As Boolean　　　　　　　 '←VBAで変更する時はTrueにするフラグ変数。既定値はFalse
  3. '------ ⇩（２）マクロを通して編集するプロシージャ---------------------------------------
  4. Sub b2_change1()　　　　　　　　　　　　 '←VBAで制御された行動
  5. 　Dim in_str As Variant
  6. 　in_str = InputBox("入力値は？")　　　　　 '←入力値を促す
  7. 　vba_change = True　　　　　　　　　　　 '←VBAが処理中のフラグ（True）を立てる
  8. 　　Range("b2").Value = in_str　　　　　　　 '←入力値をセルに書き込む
  9. 　vba_change = False　　　　　　　　　　　 '←VBAの処理が完了したフラグ（False）を立てる
  10. End Sub
  11. '------ ⇩（３）セルを書き換えた時に発生するイベントプロシージャ--------------------------
  12. Private Sub Worksheet_Change(ByVal Target As Range)　　　 '←人による行動(値を書き換えた)を検知
  13. 　If Target = Range("b2") Then　　　　　　　　　　　　　 '←それが変更しないで欲しいセルだった時
  14. 　　If vba_change = False Then　　　　　　　　　　　　　 '←VBAの処理中でなかった(人による操作)時
  15. 　　　Application.EnableEvents = False　　　　　　　　　 '←イベント停止（Undoでｲﾍﾞﾝﾄが発生しないようにする）
  16. 　　　　Application.Undo　　　　　　　　　　　　　　　 '←１つ前の状態に戻す
  17. 　　　Application.EnableEvents = True　　　　　　　　　　 '←イベント再開（通常状態に戻す）
  18. 　　End If
  19. 　End If
  20. End Sub
  21. '------ ⇩（４）シートをアクティブにした時に発生するイベントプロシージャ　------------------------------------
  22. Private Sub Worksheet_Activate()
  23. 　Application.EnableEvents = True　　　　　　　　　　 '←イベント開始（通常状態にしておく）
  24. 　vba_change = False　　　　　　　　　　　　　　　　　 '←フラグは False にしておく
  25. End Sub
図２－２

コードの上の方から説明していきます。
（１）　２行目は、プロシージャ間で共通して使う変数宣言です。「ＶＢＡで値を変更中」のフラグの役割を持ち、ＶＢＡで値を変更中には True に設定します。

（２）　４～１０行目は、ワークシート上の「Undo」と書かれたコマンドボタンを押して作動するプロシージャです。「人によるセル操作ではなく、マクロ側の制御」の代用として今回作ってあります。本サイトの他の項目で言えば「セルに入ったらカレンダーが表示されクリックする事で日付が入力される」（it-014A.php） 様なマクロです。
６行目は、一般的な InputBox で、返り値を in_str という変数に入れています。
８行目でInputBoxで入力された値をＢ２セルに書き込んでいますが、その直前の７行目で「ＶＢＡで値を変更中」フラグをTrueにし、書き込みが終わった９行目でフラグをFalseに戻します。
８行目で値を書き込んだ瞬間に Worksheet_Change イベントが発生し、１２行目からのイベントプロシージャ（３）が反応します。

（３）　１２～２０行目は、このワークシートのセルの値を変更された時に発生するイベントプロシージャです。
１３行目は、発生したイベントがどのセル位置で起こったのかを調べています。セル位置は、イベントプロシージャの引数である Target で調べます。Target は Range 型なので「Ｂ２セルか否か」を確かめたいのであれば「Range("B2")」と比較します。また R1C1 形式で比較したいのであれば「If Target.row = 2 And Target.column = 2 Then」と行と列を分割させて比較します。
１４行目は、変数vba_change の状態を調べています。もしVBAが値を変更中（=True）であるならば、１６行目の「１つ前に戻す」処理をしてはいけないからです。「もし True だったらSUBプロシージャを抜ける（If vba_change = True Then Exit Sub）」という処理でも問題ありません。
１６行目は「１つ前に戻す」処理をしています。つまりは「人による編集を１回は認めた後、元の状態に戻す」ということをしています。ですから一瞬ですが人による変更結果がセル値として表示された後に元に戻ります。
１６行目のUndoを実行すると「セル値を変更した」というイベントがまた発生してしまいます。発生するとまた１２行目から実行し始め、ふたたびUndo 処理をします。するとまたイベントが発生しUndo処理をし・・・・　と繰り返し、Undoが出来ないところまでくると「実行時エラーが発生」しマクロが停止します。
これを防ぐためには、「Undoを実行している間はイベントを発生させない処理」をする必要があり、それが１５行目になります。Undo処理が完了したら、すぐに１７行目でイベントを再開させます。

（４）２２～２５行目は、初期設定のプロシージャです。
２３行目は、イベント発生を可能（True）にする設定です。通常は True なので特別に記述する必要はないのですが、なんらかの異常（イベント発生停止（＝False）処理の後、イベント再開（＝True）をする前にマクロが停止してしまった。又は、プログラムミスでイベント再開のコードを通貨しなかった　など）でイベント発生停止のままの状態になる場合も考えられます。「ScreenUpdating」などはマクロが終わると元の状態（＝True）に自動的に戻るのですが、「EnableEvents」は戻りません。
今回は「Worksheet_Activate」に記載しましたが、システムの「必ず通るプロシージャ」に記載すればＯＫですし、異常が発生した際には「リセットボタン」の意味合いのあるプロシージャに登録するのも良いと思います。
２４行目は、「ＶＢＡで値を変更中フラグ」の初期値をFalse（人が操作しているモード）にしておきます。尚、Boolean型（True か False）の既定値はFalseですので記述し忘れても問題ありませんが、明示的に記述するのが良いと思います。

３．書き換えられないようにプロテクトしてしまう方法

２つ目の方法は、他のサイトでも良く紹介されている「シートを保護する」方法です。

３－１．シート保護解除工程を自作する方法

「シート保護」のProtectメソッドには色々なパラメータを設定できますが、パラメータを付けずに「素のProtectメッソド」を使おうとすると、下のサンプルデータの「Sheet2」のようになります。
「セル値を元に戻す」方法と同様に、Ｂ２セルを対象にコードを作っています。

図３－１

このワークシート（Sheet2）に記載されているＶＢＡコードは以下の通りです。
  1. '------ ⇩（５）マクロを通して編集するプロシージャ　-----------------------------------------------------------
  2. Sub b2_change2()
  3. 　Dim in_str
  4. 　in_str = InputBox("入力値は？")　　　　　 '←入力値を促す
  5. 　ActiveSheet.Unprotect　　　　　　　　　　 '←シートの保護を解除する
  6. 　　Range("b2").Value = in_str　　　　　　 '←入力値をセルに書き込む
  7. 　ActiveSheet.Protect　　　　　　　　　　　 '←シートを保護する
  8. End Sub
  9. '------ ⇩（６）シートをアクティブにした時に発生するイベントプロシージャ　-----------------------------------------
  10. Private Sub Worksheet_Activate()
  11. 　ActiveSheet.Unprotect　　　　　　　　　　　 '←シート保護解除
  12. 　Cell.Locked = False　　　　　　　　　　　 '←シート全面のロック解除
  13. 　Range("b2").Locked = True　　　　　　　　 '←Ｂ２セルのロック実施
  14. 　ActiveSheet.Protect　　　　　　　　　　　 '←シート保護実施
  15. End Sub
図３－２

（５）　２～８行目は、「シート保護」と書かれたコマンドボタンをクリックした時に動作するプロシージャです。
４行目は図２－２と同様で InputBox で入力値をユーザに求め、返り値を　in_st　r変数に代入しています。
６行目はInputBoxで入力された値をＢ２セルに書き込んでいますが、その直前の５行目で「シート保護解除」をし、書き込みが終わったら７行目で「シート保護」を再び実行しています。

（６）　１０～１５行目は、初期設定のプロシージャです。今回は Worksheet_Activate イベントプロシージャに記載していますが、ユーザに画面を触らせたく無い状態になった時に実行される所に記載して下さい。
１１行目ではシート保護の解除をしています。１３行目の設定をしようとした時にシート保護されているとエラーが発生してしまう為です。
１２行目ではシート全面のロックを外し、１３行目で目的のセル（ここではＢ２セル）のロックを実施します。尚、シート全面を指定するには「Cell.」を使用します。決して「Range("A1:"XFD1048576)」などは使わない様にして下さい。Excelのバージョンでシートサイズも変わりますし、なにしろ間違いの元になります。
１４行目でシートに保護を掛けます。これでＢ２セルは編集することが出来なくなり、編集しようとすると図３－３のような注意コメントが出ます（これはプログラムエラーではありません）。

図３－３

2020/04/30追記項目

３－２．UserInterfaceOnlyパラメータでユーザー操作のみ変更をロックする方法

「シート保護」のProtectメソッドには「UserInterfaceOnly」というパラメータがあります。これはパラメータの文字通り「ユーザーの操作のみ」Protect(＝画面をロック）するというものです。
図３－４は見かけの画面ですが、これは図３－１の時と全く変わりません。


図３－４

このワークシート（Sheet3）のコードは図３－５で、図３－２のコードに対しての変更点は以下の通りです。
　①５行目・７行目が不要
　②１４行目のProtectメソッドに「UserInterfaceOnly:=True」パラメータを追加
  1. '------ ⇩（７）マクロを通して編集するプロシージャ　-----------------------------------------------------------
  2. Sub b2_change3()
  3. 　Dim in_str As Variant
  4. 　in_str = InputBox("入力値は？")　　　　　 '←入力値を促す
  5. 　'ActiveSheet.Unprotect　　　　　　　　　　 '←シートの保護を解除する
  6. 　　Range("b2").Value = in_str　　　　　　 '←入力値をセルに書き込む
  7. 　'ActiveSheet.Protect　　　　　　　　　　　 '←シートを保護する
  8. End Sub
  9. '------ ⇩（８）シートをアクティブにした時に発生するイベントプロシージャ　-----------------------------------------
  10. Private Sub Worksheet_Activate()
  11. 　ActiveSheet.Unprotect　　　　　　　　　　　 '←シート保護解除
  12. 　Cell.Locked = False　　　　　　　　　　　 '←シート全面のロック解除
  13. 　Range("b2").Locked = True　　　　　　　　 '←Ｂ２セルのロック実施
  14. 　ActiveSheet.Protect UserInterfaceOnly:=True 　 '←シート保護実施
  15. End Sub
図３－５

１４行目のProtectメソッドの後に、パラメータとして「UserInterfaceOnly:=True」を付けています。
これは「ユーザーの操作のみロックする」という意味ですが、逆に言えば「マクロによる操作のみ許可する」ことになります。
そのため、シートをアクティブにする時（(８)のイベントプロシージャ）にこの設定をしておくことで、セル値を変更するプロシージャ（７）側ではシート保護・解除の操作をいちいちする必要がなくなります。

「UserInterfaceOnly」は良い事ばかりの様ですが、例えば「少し知識のあるユーザーがマクロを作り、勝手に操作」すると、簡単に書き換えられてしまいます。「マクロ操作は許している」のですから当然です。パスワードを掛けておいても同じです。
図３－５の方にするのか、図３－２の方が良いのかは状況により判断するしか無さそうです。


４．さいごに

「どの方法が正しい」ということはないのですが、一般的にはシート保護の方が有名です。画面が既に固定（完成）されている場合は確かにシート保護の方が楽で、いちいちマクロで設定せずに作り手が事前に設定する場合が多いと思います。
反対に、内容によってセルを保護したりしなかったりするような流動的である場合には、１番目の元に戻す方法もありでは　と思います。
他にも方法を思いついたら追記していきます。


添付ファイル更新（2020/04/30） セルの値を保護するマクロ（it-015A.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始）
解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。