【エクセルとVBA】エクセルシート上で日付以外を入力禁止にする方法｜もしも普通のサラリーマンがお金とITを学んだら

VBAなしでは入力規則を使って制限する。
しかし、これでは「２」など数値を打っても制限されない。不十分。
VBAで制限する方法を紹介。コピペでOK
1. シートの一部をテキストボックス化
2. yyyymmddで入力してもyyyy/mm/dd形式に変更
3. yyyy/mm/dd形式の入力はそのまま
4. ありえない日付（2022/05/56など）を入力しても受け付けないようにする
5. 文字列も数値ももちろん受け付けない

こんにちは、hokkyokunです。

依頼書などを作る際に日付を入力してほしいのに
文字列だったり、数字だったりを入力してくる。。。。

日付以外を受け付けない方法として
ユーザーフォームのテキストボックスを受け付けない方法の記事を書きました。

ワークシート上でも入力データを制限する需要があると思いますので、
プログラムを組んでみました。

Contents

VBAを使用しないで制限する方法
コード
使い方

VBAを使用しないで制限する方法

入力規則を活用します。

「入力規則」の場所
ホームタブ　⇒　データの入力規則
設定方法
1. 制限をかけたい範囲を選択
2. データの入力規則を開く　⇒　設定タブを選択　
  1. 入力値の種類を「日付」
  2. データを「次の値以上」
  3. 次の日付からを「1900/1/1」としてOK
文字列などを入力してもデータが打てなくなる。

B3に入力制限をかけてみました。
試しに「ｆ」と打ってみると制限されています。

確かにある程度はこれで制限はできます。
が、結構ザルな設定で全然不十分です。
例えば同じセルで「2」と打っても制限はかけられません。
処理としては中途半端な気がします。

コード

プログラムを組んでみました。
下記のコードをコピペして、目的のシートに貼り付ければ処理が動きます。

Private Sub Worksheet_Change(ByVal Target As Range)

'======================================================================
'範囲＝シート上のテキストボックスの位置をここで指定
'出力先は範囲の右となりに設定
Dim Rng As Range
Set Rng = ActiveSheet.Range("B2")
'======================================================================


'範囲を指定してそこ以外ではプロシージャを終了させる
If Intersect(Target, Rng) Is Nothing Then
    Exit Sub
End If

'イベントを一時停止する⇒これをしないと、何回もこのプロシージャが動いてしまいおかしくなる。
Application.EnableEvents = False

'日付であれば、出力先に転記
If IsDate(Target.Value) Then
    Rng.Offset(, 1).Value = Target.Value

Else
    '日付でない場合は入力データが数字かつ文字数が8（yyyymmddで8文字)の場合は
    'yyyy/mm/ddに変換
    'ありえない日付の場合はerr1でエラー処理させる
    If TypeName(Target.Value) = "Double" And Len(Target.Value) = 8 Then
        On Error GoTo err1
        Rng.Offset(, 1).Value = CDate(Left(Target.Value, 4) & "/" & Mid(Target.Value, 5, 2) & "/" & Right(Target.Value, 2))
        On Error GoTo 0
        
    '上記でない場合（入力データが文字列や8桁以外の数値など）は処理をしない。
    Else
        MsgBox ("日付の入力の仕方に誤りがあります。")
        Rng.Offset(, 1).ClearContents
    End If
End If

err1:
    If err.Number = 13 Then
        MsgBox ("日付の入力の仕方に誤りがあります。")
        Rng.Offset(, 1).ClearContents
    End If
    
'後始末、処理を整える。
Target.NumberFormatLocal = "G/標準"
Target.ClearContents
Rng.Offset(, 1).NumberFormatLocal = "yyyy/mm/dd"

'イベントトリガーを復活させる
Application.EnableEvents = True
End Sub

使い方

シート上の準備

入力する枠＝テキストボックスをどこに設置するか決めてください
テキストボックスの横は出力結果を表示するので空けておいてください
ついでに周辺のデザイン整えて見やすくしておくとよいと思います

VBEにプログラムをコピペする。

VBEを開く（開き方はAlt ＋ F11)
右の大きな画面（下記では黒ですが、初期設定では白）にプログラムをコピペ
上の部分にセル範囲を設定する部分があるので、自由に変更
ただし、セルは一つにしてください。

実際に入力してみる

「20」と入力すると
メッセージボックスが出て警告します。
このあと、テキストボックス内に入力した値は消されます。

「ああああ」と入力しても同様です。

「2022/5/5」と打つと、正常に打たれたと判断し、出力結果に表示されます。
ちなみに「20220505」と打っても同様の結果になります。

「20220556」とありえない日付を打っても警告が出ます
「2022/5/56」でも同様です。

使用した重要構文

TypeName(varname)
引数：varnameに値を渡す
戻り値：文字列で以下のデータ型を返す

簡単に確認してみます。

Intersectメソッド

Intersect (Arg1, Arg2, Arg3, ~,Arg30)
引数：Arg1とArg2は必須。Arg3以降は省略可
　　　それぞれ範囲を指定
戻り値：Arg1とArg2で共通の範囲がある　⇒　その範囲を戻り値として返す。
　　　　　　　　　　　共通の範囲がない　⇒　Nothingを返す。

よって
範囲2→表の範囲
範囲1→今回入力が起こったセル範囲＝Target
となります。

私が最初にVBAを学習するのに買った本です。
今でも辞書的に見直すことがあります。

リンク