入力規則（入力制限、選択式入力）　味はソコソコ？Excel Tips（しお味）

入力規則その2

入力規則その1では、主に選択式の入力に関する例を見てきましたが、ここでは入力規則のもう一つの機能の、データーを入力する場合、いかにおかしなデータを入力しないようにするかと言う入力時のフィルター機能についての例を説明します。
また、その1の続きになりますが、選択式入力の応用的な複数階層（前の選択値によって次の選択肢が変わる様な選択式入力方法）についての例を説明します。この様なやや複雑な選択方法もよくつかわれますので、おぼえておいて損は無いです。

ここで用いるのはその1と同じValidationオブジェクト関連です。
Validationオブジェクト情報を取得するには、RangeオブジェクトのValidationプロパティを使用します。
Addメソッドを使用して、その規則を一定の範囲に追加し、新しいValidationオブジェクトを作成します。

（公式情報）
https://docs.microsoft.com/ja-jp/office/vba/api/excel.validation

このページでは、まずは入力規則の基本である、入力制限について説明します。ただ、単純な入力制限・・・ある数値の範囲、ある日付の範囲の規制の場合、vbaなんか使わなくてもexcelのメニューで「データ」-->「データの入力規則」で設定出来ます。まあ、もっとややこしい、場合によって制限範囲を変えたりとか言うことがあれば vbaの出番があるかもしれません。

●入力制限の記述例
With Range("a3:a13").Validation　　　　　　　　　　：制限の設定範囲
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="10"
　 .InputMessage = "データを入力してください"　　　：入力枠がフォーカスされた時の表示
　 .ErrorMessage = "入力されたデータは範囲外です"　：制限値を外れた場合の表示
End With

ここでのポイントは、Operator:=xlBetween, Formula1:="0", Formula2:="10"で、0～10の間の整数値（xlValidateWholeNumber）を受け付ける。それを外れたら「入力されたデータは範囲外です」の表示を出して停止します。（入力出来ません）
ここでの各パラメータにより（Type、AlertStyle、Operator、Formula1、Formula2）様々な入力制限、入力値が外れた時のアクションが出来るようになります。
以下、パラメータの説明です。

Formula1[省略可]	Formula2[省略可]
データの入力規則での条件式の最初の部分を指定します。	データの入力規則での条件式の 2 番目の部分を指定します。

Type[必須]	AlertStyle[省略可]	Operator[省略可]
xlValidateInputOnly （すべての値）	xlValidAlertStop （停止:入力不可）	xlBetween （Formula1とFormula2の間）
xlValidateWholeNumber （整数）	xlValidAlertWarning （注意：入力可）	xlEqual （Formula1と等しい）
xlValidateDecimal （小数点）	xlValidAlertInformation （情報：入力可）	xlGreater （Formula1より大きい）
xlValidateTextLength （文字列：長さ指定）	---	xlGreaterEqual （Formula1以上）
xlValidateList （リスト）	---	xlLess （Formula1より小さい）
xlValidateDate （日付）	---	xlLessEqual （Formula1以下）
xlValidateTime （時刻）	---	xlNotBetween （Formula1とFormula2の間以外）
xlValidateCustom※ （ユーザー設定）	---	xlNotEqual （Formula1と異なる）

※xlValidateCustomの場合は、Formula1:="=COUNTIF(B5:B10,"女")>2"の様に、判別する式を自由に入れることが出来ます。

アラートダイアログの種類

xlValidAlertStop	xlValidAlertWarning	xlValidAlertInformation

範囲外のデータ入力不可	範囲外のデータ入力注意（入力可）	範囲外のデータ情報のみ（入力可）

入力規則の例（数値データの範囲の制限）

この例は、0～10の間の整数のみを入力させる場合の設定です。
制限値範囲外の場合は、そのデータは入力は出来ません。

●ここでの基本的なパラメータ
Type:=xlValidateWholeNumber　　：整数
AlertStyle:=xlValidAlertStop　　　：範囲外の場合は入力不可
Operator:=xlBetween　　　　　　：Formula1とFormula2との間

データ範囲の制限の例

  Sub inputc1()  '■■■入力規則の設定（範囲の制限）■■■

      Worksheets("sheet1").Range("B5").Select  '規則適用範囲の設定

      With Selection.Validation
          .Delete
          .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="10"
          .InputMessage = "データを入力してください"
          .ErrorMessage = "入力されたデータは範囲外です"
      End With

  End Sub

入力規則の解除

  Sub clr1()  '■■■入力規則解除（範囲の制限）■■■

      Worksheets("sheet1").Range("B5").Select  '規則適用範囲の設定

      With Selection.Validation

          .Delete

      End With

  End Sub

入力規則_入力制限（数値）（ダウンロード）

入力規則の例（日付データの範囲の制限）

この例は、日付の入力制限で、ある日付以降のみを入力させる場合です。
ただ、制限値範囲外でも、入力は出来る状態になっています。

●ここでの基本的なパラメータ
Type:=xlValidateDate　　　　　　　　：日付
AlertStyle:=xlValidAlertWarning　　　：注意のダイアログ（入力は可能）
Operator:=xlGreaterEqual　　　　　　：Formula1（2021/1/1）より以降

日付データの範囲の制限則

  Sub inputc5()    '入力規則解除（日付範囲の制限）

      With Worksheets("sheet1").Range("B5").Validation
          .Delete
          .Add Type:=xlValidateDate, AlertStyle:=xlValidAlertWarning, Operator:=xlGreaterEqual, Formula1:="2021/1/1"
          .InputMessage = "データを入力してください（日付　20**/**/**）"
          .ErrorMessage = "入力されたデータの値が範囲外です"
      End With

  End Sub

入力規則_入力制限（日付）（ダウンロード）

選択式入力の複数階層入力規則（2階層固定枠）

入力規則の1では選択式入力の選択肢の参照方法について説明しましたが、ここでは選択の構成（階層選択）について説明します。単純な選択入力は最もよく使われますが、さらに進んで複数の入力階層があり、前の選択したものによってその次の選択肢が変化する様な形式のものもよく使われます。この複数階層の選択肢について説明します。

1段階目選択肢（くだもの）　2段階目選択肢（品種）
もも　　　　　　　　　-->　ちよひめ、白鳳、あかつき、なつっこ、ゆうぞら
りんご　　　　　　　　-->　ふじ、トキ、紅玉、世界一、ジョナゴールド、陸奥
　：

というような感じで、始め選んだ選択肢によって次に出てくる選択肢がどんどん変わります。なんか難しそうな気もしますが、2階層までならばvbaも使わずに、入力規則の手動設定で出来ます。まずは、そちらから説明します。
方法の流れは以下のとおりです。

手動設定（データは上記のくだものを使います）

1）参照元のデータを設定します。	参考図
参照用のシートを別に作成してそこに参照元データを展開します
2）個々のデータエリアに名前を付ける
1階層目（くだもの）2階層目（個々のくだものの名前）
3）入力規則の設定をする
1階層目の入力セルにカーソルを合わせた状態で、「データ」->「データの入力規則」で出てくるダイアログで設定。
次に2階層目の入力セルにカーソルを合わせた状態で、「データ」->「データの入力規則」で出てくるダイアログで設定。

この例は、一階層目（R3C2）、二階層目（R3C3）のセルとなっています。2階層目の設定で参照元を「=INDIRECT(R3C2)」とするところがポイントです。
ここでのINDIRECT関数は、あらかじめ設定してある名前の内容を展開して返す機能があり、1階層目で選択された、くだものの名前から、その2階層目にあらかじめ設定してあった品種名を返すのでその結果、1階層目で選択したくだものの、品種が2階層目にでてくるわけです。

百聞は1見にしかず・・・現物をみたほうが早いです。
入力規則_2階層固定枠（ダウンロード）

一方、同じ入力規則をvbaで設定することを、考えるとindirect関数みたいな便利な、vbaメソッドが残念ながらありませんがもちろん、普通に出来ます。入力規則その1で、formula1の設定のやりかたには名前、範囲、文字列の3つの方法がある事を説明しましたがここでも、1階層目は範囲の名前での設定を使い、2階層目のformula1の設定に、その方法を利用します。具体的には、参照元の一覧表から、1階層目の選択肢に対応する、2階層目のデータを特定してそのデータをformula1に設定します。 3種類の方法にどれも利用出来ますが、ここではデータの文字列化（カンマ区切りの文字列にする）の方法で行いたいと思います。

vbaでの設定※参照元データは手動設定と同じ物を使います

（基本的な処理の流れ）
1）あらかじめ1階層目のデータの範囲の名前を設定しておく。（手動設定と同じ）
2）名前に基づいて、1階層目の入力規則の設定をする。
3）1階層目の入力値に対応する、2階層目のデータを特定する。
4）特定された2階層目のデータをカンマ区切りの文字列にする。
5）出来た文字列を、2階層目の入力規則の設定に用いる。

以下、そのコードです。
（1階層目の入力規則設定）

  Sub inputc7()  '入力規則_2階層のリスト選択の設定1

      With Worksheets("sheet1").Range("B11").Validation
          .Delete
          .Add Type:=xlValidateList, Formula1:="=くだもの"   '範囲の名前で設定
      End With

    Call getnt

  End Sub

（2階層目の入力規則設定：1階層目の入力値から対応する2階層目のデータを文字列にする）

  Sub getnt()  '入力規則の設定　範囲の要素を抜き出して文字列化する　(例）Formula1:="ふじ,トキ,紅玉,世界一,ジョナゴールド,陸奥"の形で元データを設定

    Dim i As Long                                        'ループ用変数
    Dim kkk As String                                    '名前の内容を蓄積して収容
    Dim nitem As String                                  '名前範囲の個々のアイテム名を収容

    Worksheets("sheet1").Cells(11, 3) = ""               '2階層目の欄をクリア

    If Worksheets("sheet1").Cells(11, 2) <> "" Then      '1階層目の欄の空白確認

      For i = 1 To Range(Cells(11, 2)).Count             '名前設定範囲の内容の個数をカウントしてループ処理

           nitem = Range(Cells(11, 2)).Item(i).Value     '一旦名前の内容をnitemに収容
           kkk = kkk + "," + nitem                       'カンマ区切りの文字列にする

      Next i

       kkk = Right(kkk, Len(kkk) - 1)                    '1文字目のカンマを削除

     '■■■入力規則を設定する■■■
      With Worksheets("sheet1").Range("C11").Validation
          .Delete
          .Add Type:=xlValidateList, Operator:=xlEqual, Formula1:=kkk    'カンマ区切りの文字列による設定
      End With

    End If

  End Sub

（1階層目の入力の変化をトリガーとして上の文字列処理を行う：ワークシートモジュール）

  Private Sub Worksheet_Change(ByVal Target As Range)  '入力規則設定　1階層目の範囲が変化した場合にgetntを実行　：　チェンジイベント発生時実行

  Worksheets("sheet1").Activate

     '2階層の入力規則（名前の利用の処理：2階層目への処理のトリガー）/////////////////////////////////////////
     If Intersect(Target, Worksheets("sheet1").Range("B11")) Is Nothing Then  '1階層目の欄が変化した場合の処理

          Else
          Call getnt

     End If

  End Sub

（入力規則の解除処理）

  Sub clr7()  '入力規則_2階層のリスト選択の設定の解除

      Worksheets("sheet1").Range("B11") = ""

      With Worksheets("sheet1").Range("B11").Validation

          .Delete

      End With

  End Sub

入力規則_2階層固定枠（ダウンロード）
※このリンクは上にある「選択式入力の複数階層入力規則（2階層固定枠）」の「入力規則_2階層固定枠（ダウンロード）」と同じものです

入力規則その2（まとめ）

以上のTIPSをまとめたファイルです。お時間の無い方におまとめ編です。
入力制限や2階層選択入力について実際に動かして試せます。

入力規則その2（まとめ）ダウンロード