Excelの入力規則が破られてしまう

Question

Excelの入力規則を使って、プルダウンを作りました。

普通に、プルダウンの中の項目以外を入力するとアラートが表示され、
入力が拒否されるのですが、入力セルで、右クリック→「ドロップダウンリストから選択」から入力すると、
リストにない項目でも入力されてしまいます。

これを回避する方法はあるのでしょうか。
よろしくお願いします。

A4がプルダウンになっている。

普通に「あ」と入力すると拒否される

右クリック→「ドロップダウンリストから選択」から入力すると入力が成功する

Comments

[rura]

差し支えなくば、ソースコードをご記載下さい

[null368]

すみません。Excelメニューバーの「データ」＞「データ入力規則」で作成したので、ソースコードはありません。

Answer 1

これってExcelのバグなんですかね？
対策として３つ考えました。

1. 対象セルの上に空セルを入れる（入力されたセルが連続しているとドロップダウンリスト化されてしまうので）
2. VBAで入力チェックを行う（Changeイベントで）
3. 「ドロップダウンリストから選択」メニューを無効化する

1番がお手軽かなと思います。
レイアウト上無理だとしても、空行を追加して非表示にしておけば良いですし。

---

追記
2のサンプルコードを書いておきます。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 1 Then ' チェック対象にするセルを指定
        If Target.Validation.Value = False Then
            MsgBox "入力エラー", vbCritical
            Application.Undo ' 入力前の状態に戻す
        End If
    End If
End Sub

Comments

[jawa]

横から失礼します。m(__)m

「ドロップダウンリストから選択」からも抜け道があるとは、今回初めて知りました。
実はこれ以外のに抜け道に、セルのコピー＆ペースト（値の貼り付け）というものもあります。

抜け道を塞ぐことが目的なら、２番しか残りそうもないですね(^_^;

しかし、Excelに「無効データのマーク」という機能が備わっているということは、こういった無効なデータを入力する術があることがわかっているけど対策はしていない、ということなんでしょうか(-''-;

[ttyp03]

あ、ホントだｗ
そうなると2番一択ですね。

[null368]

ご回答ありがとうございます！
説明不足で申し訳ないのですが、もし不適切な値が入力された場合は、入力された瞬間にアラートを出したいです。それもVBAで可能なのでしょうか？

それと、セルのコピー＆ペーストの抜け道は、許容範囲なので、ttyp003さんの「ドロップダウンリストから選択」メニューを無効化する の詳細が気になります。

[ttyp03]

すみません、回答遅くなりました。
コピペが許容範囲でリストから選択は許さないというのは矛盾してそうですが、とりあえずご要望のメニューの無効化です。
Application.CommandBars("Cell").FindControl(, 1966).Enabled = False
これで無効化はできますが、Excel全体に効いてしまいますので、対象のブック以外も同時に開くことがあるのであれば、こまめな制御が必要になります。
ブック単位の制御でよければWorkbook_OpenイベントとWorkbook_BeforeCloseイベントにでも入れて、TrueとFalseを制御すればOKです。

[ttyp03]

対策2のコードを追記しておきましたので参考にしてください。

Answer 2

個人的には「ドロップダウンリストから選択」よりも「値の貼り付け」の方がよく使われている、難易度の低い操作な気がするので。
異常値を「ドロップダウンリストから選択」で入力する行為はNG、異常値を「コピーして値の貼り付け」はOKという部分がちょっと引っ掛かりました。

コピペなんてしない（できない？）のなら話は分からないでもないのですが。。

一応、参考までにセル入力値のチェックを行うサンプルを提示します。
(ttyp03さんの②案です。)

Dim oldCell As Range    'チェック対象セル

'セル移動イベント
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    If oldCell Is Nothing Then
        'チェック対象セルが未設定の場合
        '今回の移動後のセルを次回のチェック対象セルとして保管して処理終了
        Set oldCell = Target
        Exit Sub
    End If
    
    If oldCell.Column = 1 Then
        'A列なら入力規則に反していないかをチェック
        If oldCell.Validation.Value = False Then
            'NGの場合、メッセージを表示
            MsgBox "範囲外の値です。"
            
            'チェック対象セルにフォーカスを戻す
            oldCell.Select
            Exit Sub
        End If
    End If
    
    '移動後のセルを次回のチェック対象セルとして保管
    Set oldCell = Target
End Sub

今回はVBAタグではなくExcelタグでのご質問でしたので、出過ぎた真似だったかもしれませんが、参考になれば幸いです。

Comments

[jawa]

ちょうどttyp03さんからも提示があったようで、ほんと出過ぎた真似だったかもしれません(^_^;

[null368]

とんでもないです！
ありがとうございました！

Answer 3

最初に、前回の私の回答が完全に的外れな回答であったことを訂正してお詫びします。
大変申し訳ございませんでした。

改めて、こちらでも色々調べましたが、どうやら
0. データの入力規則で設定した「リスト」
0. 右クリックの「ドロップダウンリスト」

はお互い全く異なるものであるため、入力規則のリスト設定値以外のデータが
入力されてしまう、ということが分かりました。

1.のリストは特定のセル範囲内の任意の値を入力するものですが、2.のリストの場合は
「同じ列のデータの中から任意のデータを連続して入力する場合」に利用する際に
利用できるものです。
参考：「インストラクターのネタ帳」の「リストを作らずにドロップダウン−ドロップダウンリストから選択」より

2.の場合、提示された例では「あ」「壱」というリストが表示されて、それからの入力となるはずです。

このため、入力規則の「リスト」とは異なる値が入力可能となる、というもので、
Excelのバグではなく、ある意味「表現が紛らわしいが、入力には便利な」機能です…。

回避方法ですが、ttyp03様やJawa様のマクロが一番効果的かと思われます。
(イベントプロシージャ等を利用していますので、これでしたら目的を達成可能かと。)

該当のコンテキストメニューを一時的に削除する、という非常に乱暴なやり方も考えたんですが、
これはExcelのデフォルトのメニューでやると非常に危険ですので絶対に行なわないようにして下さい…
(Excelのインストールやり直し等の良くない事態を招きますので。)
→この件ですが、

CommandBars("Cell").Controls("ドロップダウン　リストから選択(K)").Delete

で一旦削除し、ブックを閉じる段階で

CommandBars("Cell").Reset

で戻す事を想定したものです。

ttyp03様のコードは私も知りませんでした…。己の未熟さを恥じるばかりです…。

ttyp03様へ
御提示のコマンド、大変参考になりました。ありがとうございます。
と同時に、横入りしながら的外れな回答をしてしまい、大変申し訳ございませんでした…。