2020/01/29
2020/04/30 改訂(Protectメソッドに「UserInterfaceOnly」パラメータを設置する方法を追加)

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




1.セルを保護する意味

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

2.書き換えても、元に戻してしまう方法

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

図2-1

このワークシート(Sheet1)に記載されているVBAコードは以下の通りです。
  1. '------ ⇩(1)変数宣言部 -----------------------------------------------------------
  2. Dim vba_change As Boolean        '←VBAで変更する時はTrueにするフラグ変数。既定値はFalse
  3. '------ ⇩(2)マクロを通して編集するプロシージャ---------------------------------------
  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. '------ ⇩(3)セルを書き換えた時に発生するイベントプロシージャ--------------------------
  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                '←1つ前の状態に戻す
  17.    Application.EnableEvents = True           '←イベント再開(通常状態に戻す)
  18.   End If
  19.  End If
  20. End Sub
  21. '------ ⇩(4)シートをアクティブにした時に発生するイベントプロシージャ ------------------------------------
  22. Private Sub Worksheet_Activate()
  23.  Application.EnableEvents = True           '←イベント開始(通常状態にしておく)
  24.  vba_change = False                  '←フラグは False にしておく
  25. End Sub
図2-2

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

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

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

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

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

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

3-1.シート保護解除工程を自作する方法

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

図3-1

このワークシート(Sheet2)に記載されているVBAコードは以下の通りです。
  1. '------ ⇩(5)マクロを通して編集するプロシージャ -----------------------------------------------------------
  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. '------ ⇩(6)シートをアクティブにした時に発生するイベントプロシージャ -----------------------------------------
  10. Private Sub Worksheet_Activate()
  11.  ActiveSheet.Unprotect            '←シート保護解除
  12.  Cell.Locked = False            '←シート全面のロック解除
  13.  Range("b2").Locked = True         '←B2セルのロック実施
  14.  ActiveSheet.Protect            '←シート保護実施
  15. End Sub
図3-2

(5) 2~8行目は、「シート保護」と書かれたコマンドボタンをクリックした時に動作するプロシージャです。
4行目は図2-2と同様で InputBox で入力値をユーザに求め、返り値を in_st r変数に代入しています。
6行目はInputBoxで入力された値をB2セルに書き込んでいますが、その直前の5行目で「シート保護解除」をし、書き込みが終わったら7行目で「シート保護」を再び実行しています。

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

図3-3

2020/04/30追記項目

3-2.UserInterfaceOnlyパラメータでユーザー操作のみ変更をロックする方法

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


図3-4

このワークシート(Sheet3)のコードは図3-5で、図3-2のコードに対しての変更点は以下の通りです。
 ①5行目・7行目が不要
 ②14行目のProtectメソッドに「UserInterfaceOnly:=True」パラメータを追加
  1. '------ ⇩(7)マクロを通して編集するプロシージャ -----------------------------------------------------------
  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. '------ ⇩(8)シートをアクティブにした時に発生するイベントプロシージャ -----------------------------------------
  10. Private Sub Worksheet_Activate()
  11.  ActiveSheet.Unprotect            '←シート保護解除
  12.  Cell.Locked = False            '←シート全面のロック解除
  13.  Range("b2").Locked = True         '←B2セルのロック実施
  14.  ActiveSheet.Protect UserInterfaceOnly:=True   '←シート保護実施
  15. End Sub
図3-5

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

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


4.さいごに

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


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

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