Excelの入力範囲を制限するには保護よりセルを消すのがお薦めです

Excelで入力した値を他のシステムに連携したり、VBAで読み込ませたりしたい場合があります。

その際に、ユーザーに想定外の場所に値を入れられてしまうと、値を読み取ることができなくなってしまい誤動作してしまいます。

そこで「セルに保護をかける」という方法でユーザーの入力範囲を制限するかと思います。

しかし、ユーザーから「保護されていると使い勝手が悪い」と言われてしまうことも多いですよね。

できる限り操作性を損なわないように、入力範囲を制限する方法を記載してみたいと思います。

普通に保護したときの問題点

普通にシートを保護してしまうと以下の問題が発生します。

  • 列や行ごとのコピーがしにくくなる
  • Ctrl + Homeなどが効かなくなる

例として、以下の表の「品名~個数」列にだけ値を入れることを考えてみたいと思います。(ヘッダー行は変更不可)

ここで考えられる方法としては

  • 「品名~個数」列 の範囲編集を許可してシートを保護する
  • 「品名~個数」列 の ロックを解除してシートを保護する

になるかと思います。

どちらも同じような動きになりますが、今回は後者を選択してみます。

1.入力したいエリアを選択

2.セルの書式設定でロックを外す

3.シートを保護する

ここでユーザーに許可したいアクションをすべて選択します。

ちなみに「ロックされたセル範囲の選択」は外すのがベターと考えています。

「ロックされたセル範囲の選択」をつけたままだと、許可されていないセルをクリックしたときに以下のメッセージが表示されます。

編集しようとする気がなくても、セルにフォーカスしただけで警告されるのは超うざいと思っているからです。

上記の1~3の手順で、必要なセルだけを入力させるということは実装できました。

ここで操作性が良くないなと感じるのは前述した以下2点です。

  • 列や行ごとのコピーがしにくくなる
  • Ctrl + Homeなどが効かなくなる

列や行ごとのコピーがしにくくなる

行自体をコピペしたい場合は、Excelの行番号を押して行を選択するのが楽ですが、それができなくなります。

選択できないセルがあった場合には、機能してくれないようです。

ちなみに「ロックされたセル範囲の選択」をつけたままだと、 エラーメッセージも表示されてしまい使い勝手が更に良くないです。

Ctrl + Homeなどが効かなくなる

Ctrl + Homeが効かなくなります。

先頭セルがロックされているため移動がキャンセルされてしまうようです。

これはよく使うショートカットだと思うので、動作しなくなってしまうのは嫌ですよね。

使わない列は保護ではなく消せ

「列や行ごとのコピーがしにくくなる」の解決策としては、保護ではなく使わせない列や行を消してしまうのが有効です。

消すといっても、普通に列削除するのではなく非表示にして列が無いように見せるのです。

非表示にするにはVBAを使うと楽だと思います。

サンプルコード(4列目以降を消す)

Sub hideColumn()
   Range(Columns(4), Columns(Columns.Count)).Hidden = True
End Sub

サンプルコード(7行目以降を消す)

Sub hideRow()
   Range(Rows(7), Rows(Rows.Count)).Hidden = False
End Sub

列を消す処理の結果

「品名~個数」列 以外が無くなってしまったため、ユーザーから見ると他の列は入力不可です。

この状態で列をすべて選択してロックを解除します。

これでその行にロックされているセルが無くなったので、行ごとコピペすることが出来ます

ちなみに列の途中に保護したいセルなどがあった場合は、この方法は使えません(´・ω・`)

しかし私は、その場合でも使わない列は消しておきますね。

理由は格好いいからです。

不要列や行を消した場合

不要列や行を消さない場合

どうでしょうか?

前者のほうが格好良くないですか?

今回の例は小さい表なので違和感もあるかもしれませんが、大きい表になると帳票みたいになって格好良さが増しますよ。


ウィンドウ枠の固定でCtrl + Homeを復活させる

Ctrl+Homeを復活させるには、Homeとしたいセルまでのウィンドウ枠を固定すればよいです。

大きな表であればウィンドウ枠固定はよくやりますが、今回のような小さい表でも設定します。

A2セルにカーソルを当てる

ウィンドウ枠の固定を選択します。

これでCtrl+Homeが復活します!!


コメントを残す

メールアドレスが公開されることはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)