Excel-VBA 指定する特定のセルへの入力を可能にする設定

こんにちは！健史です。

Excelでデータ入力するシートを作成して

指定する特定のエリアだけを入力できるようにしたい！

ということがありませんか。

例えば以下のシートでは、セルB3～D7までを入力エリアとして、それ以外のセルは全て入力不可にする場合です。

Excelでは、
・全セルを選択
・セルの書式設定-保護にて、[ロック(L)]、[表示しない(I)]にチェックを入れる
・入力を可能にするエリアを選択(セルB3～D7)
・セルの書式設定-保護にて、[ロック(L)]、[表示しない(I)]のチェックを外す
・校閲(R)-シートの保護(PS)
です。

それによりセルB3～D7は入力できますが、セルB3～D7以外でクリックすると
「変更しようとしているセルやグラフは保護されているシート上にあります。変更するに、シート上の保護を解除してください。パスワードの入力が必要な場合もあります。」
というメッセージが表示され入力できなくなります。

上記Excelの設定を明確に紹介しているサイトが見つからなかったので記事にしました。

目　次　

指定する特定のセルへの入力を可能にする設定
入力不可設定する範囲を変数化して指定
最後に

指定する特定のセルへの入力を可能にする設定

'シート全体の保護を解除
    ActiveSheet.Unprotect
'入力エリアの内容をクリアする場合(書式や関数はクリアしない))
    Sheets(1).Range("B3:D7").ClearContents
'シート全体のセルへ[ロック(L)]、[表示しない(I)]を設定
    Sheets(1).Cells.Locked = True
    Sheets(1).Cells.FormulaHidden = True
'入力エリアのセルへの[ロック(L)]、[表示しない(I)]設定を解除
    Sheets(1).Range("B3:D7").Locked = False
    Sheets(1).Range("B3:D7").FormulaHidden = False
'シート全体を保護
    ActiveSheet.Protect

補足します。

１.シート全体の保護を解除

[ActiveSheet.Unprotect]は、当処理を繰り返し実行する場合のことを想定した処理です。

初回実行したときは「シート全体を保護」の[ActiveSheet.Protect]が実行され、保護されています。

保護された状態ではクリアすることができないので、最初に解除します。

ちなみに、保護していない状態で保護を解除してもエラーになりません。

2.入力エリアの内容をクリアする場合

書式や関数も全てクリアする場合は、[Sheets(1).Range("B3:D7").Clear]とします。

入力不可設定する範囲を変数化して指定

設定する範囲をコンスタントで定義しておく方法、いろいろ試したのですが以下です。

'開始セル 定義
    Const cnsStaCol As Long = 2 'B
    Const cnsStaRow As Long = 3 '3
'終了セル 定義
    Const cnsEndCol As Long = 4 'D
    Const cnsEndRow As Long = 7 '7

Sub protect1()
'シート全体の保護を解除
    ActiveSheet.Unprotect
'入力エリアの内容をクリア
    Sheets(1).Range(Cells(cnsStaRow, cnsStaCol), Cells(cnsEndRow, cnsEndCol)).ClearContents
'シート全体のセルへ[ロック(L)]、[表示しない(I)]を設定
    Sheets(1).Cells.Locked = True
    Sheets(1).Cells.FormulaHidden = True
'入力エリアのセルへの[ロック(L)]、[表示しない(I)]設定を解除
    Sheets(1).Range(Cells(cnsStaRow, cnsStaCol), Cells(cnsEndRow, cnsEndCol)).Locked = False
    Sheets(1).Range(Cells(cnsStaRow, cnsStaCol), Cells(cnsEndRow, cnsEndCol)).FormulaHidden = False
'シート全体を保護
    ActiveSheet.Protect
End Sub

対象範囲が変更になる場合に、複数個所を修正しなくても良いようにするためです。

参考までに、以下では動きません。

'範囲を定義
    Const cnsRange As String = "B3:D7"

Sub protect1()
'シート全体の保護を解除
    ActiveSheet.Unprotect
'入力エリアの内容をクリア
    Sheets(1).Range(" & cnsRange & ").ClearContents
End Sub

また[Const cnsRange As Range = "B3:D7"]としてみたのですが、構文エラーになります。

最後に

「データクリア」とか「データ初期クリア」などのボタンを配置して実行してもらうことが良いと思います。

ボタン配置については、以下の記事を参考にして頂ければと思います。

・文字列の検索、複数ファイル複数シートの検索結果を出力

・マクロ''を実行できません